Improving Concurrency & Scalability of SQL Server workload by optimizing database containment check in SQL 2014\SQL 2016
Starting SQL 2012, database containment property is introduced in SQL Server database to support contained databases. As described in MSDN article here, database collation not only applies to data stored in user tables but also applies to variables, stored procedure parameters, GOTO labels. In case of contained databases, the collation of the metadata, variables, parameters, GOTO labels, cursors, is different from the database collation . Sincedatabase containment property can be updated online while the instance is running, during each execution of the stored procedure, it is important to check the database containment property to see if the property is set or changed before the query plan is compiled, to ensure the stored procedure parameters, variables or GOTO labels in the stored procedure is using the right collation setting.
Database containment property is set or read from DBTABLE, which is an in-memory data structure for every database in SQL Server. To read containment property during every execution of stored procedure, a spinlock is acquired on DBTABLE, which is a lightweight synchronization mechanism to acquire fast exclusive locks. Spinlock wait bottlenecks (also referred to as spinlock collisions) occur when concurrent threads are waiting to acquire the same lock on a data structure that is already acquired by one of the threads for exclusive reading/writing of in-memory data structure. Spinlocks are usually used for fast access and hence the threads waiting for the lock spins on CPUs - rather than yielding immediately - until it acquires the lock. To ensure the threads waiting for spinlock do not hog the CPU for long during long waits (spins), the threads are forced to yield periodically (which is referred to as spinlock backoffs). To ensure excessive spins do not cause additional CPU overhead, the sleep period between each backoff is increased exponentially.
On high end systems with large number of cores or processors (typically 32 logical processors or more), the concurrency of the SQL Server may be high enough such that if all the threads are executing stored procedures from same database, all the threads might appear to wait for spinlock on DBTABLE structure simultaneously, to read the containment property of the database. Excessive spinlock collisions can lead to increased query response time, drop in throughput and further lead to increased CPU overhead with worker threads spinning and burning the cpu cycles while waiting for spinlocks. This limits the overall concurrency, throughput, and scalability of the SQL Server on latest high end systems.
Starting SQL 2014 SP1 CU8, SQL 2014 SP2 CU1 and SQL 2016 CU1, the spinlock to check the database containment property is replaced by the “load acquire and store release” lock semantics, which is a non-blocking lock-free synchronization mechanism between the concurrent threads. This avoids exclusive spinlocks and thereby avoids the spinlock collisions between the concurrent threads executing stored procedures from same database as described earlier. This change improves the overall concurrency and scalability of the system especially if all the worker threads are simultaneously executing a stored procedure from same database.
The improvement is also documented in KB 3175883 and is further elaborated in this blog to explain the improvement in detail.
Parikshit Savjani
Senior Program Manager (@talktosavjani)