Database Containment Checks

Parikshit Savjani discusses a nice little performance optimization in the latest versions of SQL Server 2014 and 2016:

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.

On extremely busy systems, this might be a reason to update.

Related Posts

NOLOCK Doesn’t Mean No Locks

Bert Wagner points out that SELECT queries with NOLOCK can still cause blocking to occur: This is where my understanding of NOLOCK was wrong: while NOLOCKwon’t lock row level data, it will take out a schema stability lock. A schema stability (Sch-S) lock prevents the structure of a table from changing while the query is executing. All SELECT statements, including those […]

Read More

Columnstore Deadlocking

Kendra Little shows us a scenario in which querying columnstore metadata during table updates can lead to a deadlock: I was playing around with a nonclustered columnstore index on a disk-based table. Here’s what I was doing: Session 1: this session repeatedly changed the value for a single row, back and forth. This puts it into […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031