SQL 2016 – It Just Runs Faster: SOS_RWLock Redesign

Published Mar 01 2022 09:16 AM 598 Views
Microsoft
Moved from: bobsql.com

 

The SOS_RWLock is a synchronization primitive used in various places throughout the SQL Server code base.  As the name implies the code can have multiple shared (readers) or single (writer) ownership. 

 

Studying the SQL Server 2012 and 2014 implementation of the SOS_RWLock we found the core acquisition and wait list could be optimized.   SQL Server 2016 contains the design changes for the SOS_RWLock using similar design patterns and concepts leveraged by in-memory optimized objects.

 

As part of a change to improve the execution of sp_reset_connection (logical connection pooling) the SOS_RWLock redesign reduced the duration on a highly contended database by 5%.

 

‘It Just Runs Faster’ – Apply SQL Server 2016 and code paths leveraging reader/writer locks use less resources and scale better.

 

DEMO – It Just Runs Faster: SOS_RWLock

Overview

This demonstration exercises the code path for sp_reset_connection which leverages an SOS_RWLock to access the database state information.  The reset code path is always under scrutiny so the code path itself is already lean and a great way to show the SOS_RWLock improvement.   The issues arise in this and other code paths in the ability to reduce or avoid contention on shared data structures.
 

Note: Because the scalability improvement is based on a contended resource the demonstration works better on systems with 16 or more CPUs and is intended to maximize CPU to cause various levels of contention.
 

Steps

  1. Add SQL Server (Batch Requests/sec) counter to performance monitor for a SQL Server 2016 instance and SQL Server 2012 instance located on the same machine.
     
  2. Execute the following RML, OStress utility command from a Windows Command prompt on the server.  The LPC reduces network latency providing tighter stress.
     
ostress -E -Slpc:.\SQL2012 -Q”{call sp_reset_connection()}” -q -r100000 -n1024 -oc:\temp -L120
  1. Execute the following OStress command in a second, Windows Command prompt on the same machine.
    ostress -E -Slpc:.\SQL2016 -Q”{call sp_reset_connection()}” -q -r100000 -n1024 -oc:\temp2 -L120

Actual Scenarios

SQL Server 2016 has been vetted by a wide range of customers.   The positive impact of these changes has been realized by:
 

  • One world’s largest banks is able to achieve 20% better throughput without application changes 
  • Multiple large web sites report increase in page load speeds because of the sp_reset_connection scalability improvement  

Sample Results  (2.5 times faster)

Machine

56GB RAM, 8 Core Hyper-threaded enabled 2.2Ghz (Azure A7 Virtual Machine)

SQL Server

Out of the box, default installation

 

Demonstration

The following is a snapshot of performance monitor (Batch Requests/sec) as the stress tests run in parallel on the local test machine showing SQL Server 2016 scales because of changes to internal primitives such as the SOS_RWLock.

 

  • Black = SQL 2016
  • Red = SQL 2012
  • Y Scale = 1000s
Co-Authors
Version history
Last update:
‎Mar 01 2022 09:16 AM
Updated by:
We support Ukraine and condemn war. Push Russian government to act against war. Be brave, vocal and show your support to Ukraine. Follow the latest news HERE