How It Works: sp_server_diagnostics – spinlock backoffs

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" sickSpinlockType="none" sickSpinlockTypeAfterAv="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,000s 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