Novamente Spinlock
O que seria um spinlock? Essa é uma pergunta recorrente e que mal consigo explicar… parte porque é uma estrutura pouco detalhada nos artigos da Microsoft.
Para aqueles que nunca viram o “spinlock” documentado, segue algumas pistas de que ele realmente existe:
Artigo de Suporte KB835864: https://support.microsoft.com/kb/835864
On multiprocessor computers, access to these counter values must be serialized. SQL Server serializes the counter values by using a spinlock. With a spinlock, the thread that wants to acquire the spinlock tries to acquire the lock. If the lock is not available, the thread spins in a loop, periodically rechecking the availability of the resource. If the operation that occurs inside the spinlock protection has a very short duration, such as incrementing or decrementing a counter, using a spinlock can be much faster than using a kernel synchronization object and then switching from user mode to kernel mode and back. However, if the spinlock becomes highly contended, a thread may spend more time spinning and trying to acquire the lock than it does performing useful work. Because of this, spinlocks are not a good choice for protecting a heavily-contended data structure.
Blog do CSS SQL:
https://blogs.msdn.com/b/psssql/archive/2010/06/08/how-it-works-spinlock-of-type-lpe-batch.aspx
A spinlock is a lightweight, user mode synchronization object used to protect a specific structure. The goal of the spinlock is to be fast and efficient. So the ultimate goal is to see is 0 collisions and 0 spins. SQL Server 2008 contains 150+ spinlock objects to protect the various internal structures during multi-threaded access.
Recém lançado, segue um whitepaper do SQL CAT:
This paper provides in-depth information about the methodology the Microsoft SQL Server Customer Advisory Team (SQLCAT) team uses to identify and resolve issues related to spinlock contention observed when running SQL Server 2008 and SQL Server 2008 R2 applications on high-concurrency systems.
Bons estudos!