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
ostress -E -Slpc:.\SQL2012 -Q”{call sp_reset_connection()}” -q -r100000 -n1024 -oc:\temp -L120
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.