First published on MSDN on Dec 12, 2014
There are numerous articles outlining how spinlocks work so I won’t cover the details in this post. Instead, I want to focus on the
spinlockbackoffs
value recorded in the
sp_server_diagnostics
output.
Component
= System
<system
spinlockBackoffs
="0"
sick
SpinlockType="none"
sick
SpinlockTypeAfterAv="none" …
Querying select * from sys.dm_os_spinlock_stats the backoffs column is presented. This is
NOT
the same as the spinlockBackoffs presented in the sp_server_diagnostics output.
A spinlock backoff is only counted in sp_server_diagnostics when the spinlock has been declared
SICK
. Sick is the term used to indicate that the code has attempted to acquire the spinlock ownership but after 10,000
s
of spins and lightweight backoffs, for approx. 5 seconds, ownership could not be acquired. At the ~5 second point the code performs a more agressive sleep operation because the spinlock appears to be damaged or hung up in some way, or is
sick,
if you will.
The XEvent
spinlock_backoff
maps to the dm_os_spinlock_stats, backoff column.
The XEvent
spinlock_backoff_warning
maps to the sp_server_diagnostics output and is produced when the spinlock is declared sick.
The
System
component can report
ERROR
state once the spinlockBackoffs reach a count of 2 or greater for the same sickSpinlockType. This indicates that for approx., 10 or 15 seconds the spinlock could not acquire ownership, signaling a larger issue on the system. (Orphaned spinlock, CPU problems, etc…)
Bob Dorr - Principal SQL Server Escalation Engineer
