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
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.
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.
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.
Execute the following
, OStress utility command from a Windows Command prompt on the server. The LPC reduces network latency providing tighter stress.
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.