Introduction to spin locks in SQL Server

In the previous article I discussed the latch in SQL Server. At the end of the article I gave you a brief introduction to the next spin lock (Spinlock). Based on that foundation, today I'll continue to discuss spin locks in SQL Server and show you how to troubleshoot them.

Why do we need a spin lock?

as I have pointed out in the previous article, it is meaningless to place a latch on each shared data structure by using a latch to synchronize access between multiple threads of data structures. The latch is tightly tied to this: when you cannot get the latch (because someone else already has an incompatible latch), the query forces the wait and enters the pending (SUSPENDED) state. The query waits in a pending state until it can get the latch, and then it enters the executable (RUNNABLE) state. For query execution as long as there is no CPU available, the query is in the executable (RUNNABLE) state. Once the CPU is idle, the query goes into the running (RUNNING) state and finally succeeds to the latch, which is used to protect the shared data structures that are accessed. The state machine for coordinating thread scheduling implementation is demonstrated by Sqlos.  

.

Because there are too many associated latches, there is no point in using latch protection on a "busy" data structure. So SQL Server implements the so-called spin Lock (spinlocks). A spin lock is like a latch, a lightweight synchronization object used by the storage engine to synchronize access to a shared data structure thread. The main difference from the latch is that you actively wait for the spin lock--without leaving the CPU. The "Wait" on the spin lock will always occur in the running (RUNNING) state of the CPU. Rotate in your closed loop until you get a spin lock. This is called busy waiting (busy wait). The biggest advantage of a spin lock is that context switching is not involved when the query is waiting in the spin lock. On the other hand, while busy waiting for CPU cycles, other queries might be able to use them more efficiently.

To avoid too much CPU cycle waste, SQL Server R2 and subsequent versions implement the so-called exponential compensation mechanism (exponential backoff mechanism), where the thread stops spinning after some time on the CPU after hibernation. When the thread enters hibernation, an attempt to obtain a spin lock timeout is added. This behavior can reduce the effect on CPU performance.

(Additional notes: spinlock Chinese can be called spin lock. It is a lightweight, user-State synchronization object, similar to the critical section, but with a much smaller granularity than the former. It is primarily used to protect the multi-threaded concurrent access of certain specific memory objects. The spinlock is exclusive. Can only be owned by one thread at a time.

Spinlock's design goals are very fast and efficient. How does spinlock work inside? It first attempts to obtain a lock on an object, and if the target is occupied by another thread, it polls (spin) for a certain amount of time. If you don't get a lock, sleep for a little while and then continue to spin. Repeat this process until you get the possession of the object. )

spin lock and troubleshooting

The main DMV for Spin lock troubleshooting is sys.dm_os_spinlock_stats. Each row returned in this DMV represents a spin lock in SQL Server. SQL Server 2014 implements 262 different spin locks. Let's look at the various columns in this DMV in detail:

• Name: Spin lock names
• Collision: Number of threads blocked by spin lock when attempting to access a protected data structure
• Spins: Number of spin lock threads attempting to obtain spin locks in a loop
• Spins_per_collision: ratio between rotation and collision
• Sleep_time: Due to backoff thread sleep time
• Backoffs:: Number of thread Backoff for other threads to continue on the CPU

The most important column in this DMV is backoffs, which tells you the frequency of avoidance occurs for specific spin lock types. The avoidance of high frequencies will yield to CPU consumption resulting in spin lock contention in SQL Server (Spinlock contention). I've seen a 32-core SQL Server server with a CPU running at 100% without any work-a typical spin-lock competitive symptom.

Troubleshooting Spin Lock issues You can use the Sqlos.spinlock_backoff provided by the extended event. This extension event is triggered when the Backoff (Backoff) occurs. If you capture this event, you also have to make sure that you use a very good selective predicate, because avoidance in SQL Server often occurs. A good predicate can be a specific spin lock type that you have seen through the DMV just mentioned. The following code shows you how to create such an extended event session.

1 --Retrieve The type value for the Lock_hash spinlock.2 --That value was used by the next XEvent session3 SELECT *  fromsys.dm_xe_map_values4 WHEREName= 'Spinlock_types'5  andMap_value= 'Lock_hash'6 GO7 8 --Tracks the Spinlock_backoff event9 CREATEEVENT SESSION spinlockcontention onSERVERTen ADDEVENT Sqlos.spinlock_backoff One ( A ACTION -     ( - Package0.callstack the     ) -     WHERE -     ( -         [type] = 129 --<<< Value from the previous query +     ) - )  + ADDTARGET Package0.histogram A ( at     SETSource= 'Package0.callstack', Source_type= 1 - ) - GO

As you can see from the code, here I use the histogram (histogram) target on the call stack (callstack) to Bucktize. So for a particular spin lock, you can potentially generate the highest backoff (backoffs) code path to the SQL serve. You can even identify the call stack by enabling the 3656 trace flag. Here you can see the output from this extended session:

sqldk.dll! Xesospkg::spinlock_backoff::P ublish+0x138
sqldk.dll! Spinlockbase::sleep+0xc5
sqlmin.dll! Spinlock<129,7,1>::Spintoacquirewithexponentialbackoff+0x169
sqlmin.dll!lck_lockinternal+0x841
sqlmin.dll! Xactworkspaceimp::Getshareddblockfromlockmanager+0x18d
sqlmin.dll! xactworkspaceimp::getdblocklocal+0x15b
sqlmin.dll! xactworkspaceimp::getdblock+0x5a
SQLMIN.DLL!LOCKDB+0X4A sqlmin.dll! Dbmgr::opendb+0x1ec
Sqlmin.dll!sqlusedb+0xeb
Sqllang.dll!usedb+0xb3
sqllang.dll! Loginusedbhelper::usebymddatabaseid+0x93
sqllang.dll! Loginusedbhelper::fdeterminesessiondb+0x3e1
sqllang.dll! Fredologinimpl+0xa1b
sqllang.dll! Fredologin+0x1c1
Sqllang.dll!process_request+0x3ec
Sqllang.dll!process_commands+0x4a3
sqldk.dll! Sos_task::P aram::execute+0x21e
sqldk.dll! Sos_scheduler::runtask+0xa8
sqldk.dll! Sos_scheduler::P rocesstasks+0x279
sqldk.dll! schedulermanager::workerentrypoint+0x24c
sqldk.dll! systemthread::runworker+0x8f
sqldk.dll! Systemthreaddispatcher::P rocessworker+0x3ab
sqldk.dll! schedulermanager::threadentrypoint+0x226

Using the provide call stack, it is not difficult to find out where the spin lock competition takes place. The competition in that specified about zombie stack occurs in the Lock_hash spin lock type, which is the hash table that secures the lock manager. Each time a lock manager is locked or unlocked, the spin lock must be obtained in the corresponding hash bucket. As you can see, in the call stack, when the Getshareddblockfromlockmanager function is called from the Xactworkspacelmp class, the spin lock is obtained. This means that the shared database lock is attempted to acquire when competing to the database. Finally in the Lock_hash spin lock with high avoidance (backoffs), this yields to the spin lock competition.

Summary

In this article you learned about spin locks in SQL Server. In the 1th part we discuss why SQL Server needs to implement spin locks. As you can see, using a spin lock protects the "busy" shared data structures from concurrent threads from being more "inexpensive"-such as lock managers. In part 2nd we discussed in detail the spin lock competition for SQL Server How you troubleshoot, and how the call stack using identities can identify the root cause of the problem.

Thanks for your attention!

Introduction to spin locks in SQL Server