Tuesday, May 13, 2014

SQL Server Spinlock Reference

I've been troubleshooting some performance issues of late, and it was brought to my attention that there is yet another DMV that may shed some light on the subject: sys.dm_os_spinlock_stats.

First, what is a spinlock?
Per Microsoft's whitepaper Diagnosing and Resolving Spinlock Contention on SQL Server: "Spinlocks are lightweight synchronization primitives which are used to protect access to data structures.  They are generally used when it is expected that access to a given data structure will need to be held for a very short period of time."

How does a spinlock differ than a latch?
Per the whitepaper: "The main difference between a latch and a spinlock is the fact that spinlocks will spin (execute a loop) for a period of time checking for availability of a data structure while a thread attempting to acquire access to a structure protected by a latch will immediately yield if the resource is not available. Yielding requires context switching of a thread off the CPU so that another thread can execute. This is a relatively expensive operation and for resources that are held for a very short duration it is more efficient overall to allow a thread to execute in a loop periodically checking for availability of the resource."

What condition might indicate spinlock contention?
Per the whitepaper:
1. A high number of spins and backoffs are observed for a particular spinlock type.


2. The system is experiencing heavy CPU utilization or spikes in CPU consumption. In heavy CPU scenarios one may also observe high signal waits on SOS_SCHEDULER_YEILD (reported by the DMV sys.dm_os_wait_stats).


3. The system is experiencing very high concurrency.


4. The CPU usage and spins are increased disproportionate to throughput.
Options and workarounds for handling spinlock contention:
Per the whitepaper:
  1. Fully Qualified Names: Fully qualifying names of all objects will result in removing the need for SQL Server to execute code paths that are required to resolve names. We have observed contention points also on the SOS_CACHESTORE spinlock type encountered when not utilizing fully qualified names in calls to stored procedures. Failure to fully qualify these the names results in the need for SQL Server to lookup the default schema for the user which results in a longer code path required to execute the SQL.
  2. Parameterized Queries: Another example is utilizing parameterized queries and stored procedure calls to reduce the work needed to generate execution plans. This again results in a shorter code path for execution.
  3. LOCK_HASH Contention: Contention on certain lock structure or hash bucket collisions is unavoidable in some cases. Even though the SQL Server engine partitions the majority of lock structures, there are still times when acquiring a lock results in access the same hash bucket. For example, an application the accesses the same row by many threads concurrently (i.e. reference data). This type of problems can be approached by techniques which either scale out this reference data within the database schema or leverage NOLOCK hints when possible.
The diagram below provides a conceptual view of the spinlock algorithm:

Here are some additional resources I found on the topic:

Paul Randal
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock, April 2011
Advanced SQL Server performance tuning, April 2011

Thomas Kejser
Thread Synchronization In SQL Server, November 2011
Diagnosing And Fixing SOS_OBJECT_STORE Spins for Singleton INSERTS, May 2011
Secret Scale Sauce: Heavy Singleton INSERT Spinlocks, May 2011

Arvind Shyamsundar
Exotic spinlocks: XTS_MGR, January 2014

Fabricio Catae
Spinlock (Part I), July 2010
Spinlock (Part II), July 2010
Spinlock (Part III), July 2010
Spinlock Contention, December 2009