A Brief Introduction to SQL Server spin locks and SQL spin

A Brief Introduction to SQL Server spin locks and SQL spin

Why do we need spin locks?
It is meaningless to use a locks to synchronize data structure access between multiple threads before each shared data structure. The locks are closely related to this: when you cannot obtain the locks (because someone else already has an incompatible locks), the query will force the wait and the query will be SUSPENDED) status. The query is pending until the lock can be obtained, and then enters the RUNNABLE status. If no CPU is available for query execution, the query is always in the RUNNABLE state. Once the CPU is idle, the query enters the RUNNING state, and the latch is obtained successfully, which is used to protect the accessed shared data structure. Shows SQLOS's state machine for coordinating thread scheduling.

Because there are too many associated latches, it makes no sense to use the latches to protect the "busy" data structure. Therefore, SQL Server implements the so-calledSpin locks). A spin lock is like a latch. It is a lightweight synchronization object used by the storage engine to synchronize access to threads in the shared data structure. The main difference from the latch is that you actively wait for the spin lock-do not leave the CPU. The "wait" in the spin lock always happens to the running cpu. Rotate in your closed loop until the spin lock is obtained. This is the so-called busy waiting (busy wait ). The biggest advantage of the spin lock is that context switching is not involved when the query waits for the spin lock. On the other hand, busy waiting for a waste of CPU cycles, other queries may be more effective for them.

To avoid too many CPU cycles, SQL Server 2008 R2 and subsequent versions implement the so-calledExponential backoff mechanic), Where the thread stops rotating after sleep for some time on the CPU. When the thread enters sleep, the timeout for trying to obtain the spin lock is increased. This behavior can reduce the impact on CPU performance.

(Note: The Chinese version of Spinlock can be called a spin lock. It is a lightweight, user-state synchronization object, similar to the critical section, but the granularity is much smaller than the former. It is mainly used to protect the multi-thread concurrent access to some specific memory objects. The Spinlock is exclusive. Only one thread can own it at a time.

The goal of the Spinlock design is extremely fast and efficient. How does a Spinlock work internally? First, it tries to obtain the Lock of an object. If the target is occupied by other threads, it will round-robin (spin) there for a certain amount of time. If the lock is not available, sleep for a while and continue with spin. Repeat this process until you get the possession of the object .)

Spin lock and troubleshooting
The main DMV for troubleshooting spin locks isSys. 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 take a closer look at the various columns in this DMV:

Name: Spin lock name
Collision: Number of threads blocked by spin locks when attempting to access protected data structures
Spins: Number of spin lock threads trying to obtain a spin lock in a loop
Spins_per_collision: Ratio between rotation and collision
Sleep_time: Because the backoff thread sleep time
Backoffs: Number of thread backoff times for other threads to continue on the CPU
The most important column in this DMV is backoffs. For a specific spin lock type, this column tells you how often backoff occurs. High-frequency backoff will succumb to CPU consumption and cause the SQL Server to compete with the spin lock Contention ). I have seen a 32-core SQL Server with a CPU running at 100% without any work-typical symptoms of spin lock competition.

To troubleshoot the spin lock problem, you can use sqlos. spinlock_backoff provided by the extended Event. When a backoff (backoff) occurs, this extended event is triggered. If you capture this event, make sure that you use a very good selective predicates, because frequent backoff occurs in SQL Server. A good predicate can be a specific spin lock type, as you can see through the DMV just mentioned. The following code shows you how to create an extended Event session.

 -- Retrieve the type value for the LOCK_HASH spinlock. -- That value is used by the next XEvent session SELECT * FROM sys.dm_xe_map_values WHERE name = 'spinlock_types' AND map_value = 'LOCK_HASH' GO  -- Tracks the spinlock_backoff event CREATE EVENT SESSION SpinlockContention ON SERVER  ADD EVENT sqlos.spinlock_backoff( ACTION (  package0.callstack )  WHERE (  [type] = 129 -- <<< Value from the previous query )) ADD TARGET package0.histogram (  SET source = 'package0.callstack', source_type = 1 ) GO

As you can see from the code, here I amCall Stack)Used onHistogram (histogram)Target to bucktize. Therefore, for specific spin locks, you may go to the backoffs code path generated in SQL Serve. You can even identify the call stack by enabling trace flag. Here you can see the output from this extended session:

Sqldk. dll! XeSosPkg: spinlock_backoff: Publish + 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: Param: Execute + 0x21e
Sqldk. dll! SOS_Scheduler: RunTask + 0xa8
Sqldk. dll! Sos_schedks: ProcessTasks + 0x279
Sqldk. dll! SchedulerManager: WorkerEntryPoint + 0x24c
Sqldk. dll! SystemThread: RunWorker + 0x8f
Sqldk. dll! SystemThreadDispatcher: ProcessWorker + 0x3ab
Sqldk. dll! SchedulerManager: ThreadEntryPoint + 0x226

Using the provided call stack, it is not difficult to find out where the spin lock competition occurs. Competition in the specified zombie stack occurs in the LOCK_HASH spin lock type, which is a hash table that protects the lock manager. The spin lock must be obtained in the corresponding hash bucket every time the lock is locked or unlocked in the lock manager. As you can see, in the call stack, when the GetSharedDBLockFromLockManager function is called from the XactWorkspacelmp class, the spin lock is obtained. This indicates that the shared database lock is attempted to be obtained when the database is competing. Finally, in the LOCK_HASH spin lock with a very high Backoff (backoffs), this gives in to the competition of spin locks.

The above is all the content of this article, hoping to help you learn.