Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve a spin lock in SQL Server. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Why do we need spin locks?

There is no point in synchronizing access to multiple inter-thread data structures with latches, and placing a latch in front of each shared data structure. Latches are closely related to this: when you cannot get a latch (because someone else already has an incompatible latch), the query forces the wait and enters the SUSPENDED state. The query waits in the pending state until the latch can be obtained, and then enters the RUNNABLE state. For query execution, the query remains in an executable (RUNNABLE) state as long as there is no CPU available. Once the CPU is free, the query goes into the RUNNING state and finally successfully acquires the latch to protect the accessed shared data structure. The following figure shows the state machine implemented by SQLOS for coordinating thread scheduling.

There is no point in using latch protection for "busy" data structures because there are too many associated latches. 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 thread access to shared data structures. The main difference between a latch and a latch is that you are actively waiting for the spin lock-- not leaving the CPU. The "wait" on the spin lock always occurs in the RUNNING state of the CPU. Rotate in your closed loop until you get the 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 waits on the spin lock. On the other hand, busy waiting wastes CPU cycles, and other queries may be able to use them more effectively.

To avoid too much CPU cycle waste, SQL Server 2008 R2 and subsequent versions implement the so-called exponential compensation mechanism (exponential backoff mechanism), where threads stop spinning after some time of sleep on the CPU. The timeout for trying to acquire a spin lock was increased while the thread went into hibernation. This behavior reduces the impact on CPU performance.

(additional note: Spinlock can be called spin lock in Chinese. It is a lightweight, user-mode synchronization object, similar to critical section, but much smaller in granularity. It is mainly used to protect multithreaded concurrent access to some specific memory objects. Spinlock is exclusive. Can only be owned by one thread at a time.

Spinlock is designed to be very fast and efficient. How does Spinlock work internally? It first attempts to acquire a lock on an object, and if the target is occupied by another thread, it spin there for a certain amount of time. If you can't get the lock, sleep for a while, and then continue spin. Repeat this process until you get possession of the object. )

Spin lock and troubleshooting the main DMV for troubleshooting spin lock is sys.dm_os_spinlock_stats. Each line 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 columns in this DMV:

Name: spin lock name collision: number of threads blocked by spin locks when trying to access protected data structures spins: number of spin lock threads trying to acquire spin locks in a loop spins_per_collision: ratio between rotation and collision sleep_time: because dodging thread dormancy time backoffs: for other threads to continue on CPU, the most important column of thread Backoff times in this DMV is backoffs, for a specific spin lock type This column tells you how often retreat occurs. High-frequency avoidance will succumb to spin-lock competition (Spinlock Contention) in SQL Server caused by CPU consumption. I've seen a 32-core SQL Server server where CPU runs at 100% without doing anything-- a typical symptom of spin-lock competition.

To troubleshoot spin lock problems you can use the sqlos.spinlock_backoff provided by the extended event. This extension event is triggered when backoff occurs. If you catch this event, you also need to make sure that you use a very good selective predicate, because retreat often occurs in SQL Server. A good predicate can be a specific type of spin lock, as you have seen through the DMV just mentioned. The following code shows you how to create such 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--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.