Condividi tramite

SQL 2016 - It Just Runs Faster: SOS_RWLock Redesign

  • Articolo

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.

 

Bob Dorr - Principal SQL Server Software Engineer

Ajay Jagannathan - Principle SQL Server Program Manager

 

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

 

clip_image001