How It Works: sp_server_diagnostics – spinlock backoffs

Published Jan 15 2019 04:21 PM 336 Views
Microsoft
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

%3CLINGO-SUB%20id%3D%22lingo-sub-318475%22%20slang%3D%22en-US%22%3EHow%20It%20Works%3A%20sp_server_diagnostics%20%E2%80%93%20spinlock%20backoffs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318475%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2012%2C%202014%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20There%20are%20numerous%20articles%20outlining%20how%20spinlocks%20work%20so%20I%20won%E2%80%99t%20cover%20the%20details%20in%20this%20post.%26nbsp%3B%26nbsp%3B%20Instead%2C%20I%20want%20to%20focus%20on%20the%20%3CEM%3E%20spinlockbackoffs%20%3C%2FEM%3E%20value%20recorded%20in%20the%20%3CEM%3E%20sp_server_diagnostics%20%3C%2FEM%3E%20output.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Component%20%3C%2FSTRONG%3E%20%3CSTRONG%3E%20%3D%20System%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSYSTEM%3E%3CSTRONG%3E%20spinlockBackoffs%20%3C%2FSTRONG%3E%20%3D%220%22%20%3CSTRONG%3E%20sick%20%3C%2FSTRONG%3E%20SpinlockType%3D%22none%22%20%3CSTRONG%3E%20sick%20%3C%2FSTRONG%3E%20SpinlockTypeAfterAv%3D%22none%22%20%E2%80%A6%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Querying%20select%20*%20from%20sys.dm_os_spinlock_stats%20the%20backoffs%20column%20is%20presented.%26nbsp%3B%26nbsp%3B%20This%20is%20%3CSTRONG%3E%20NOT%20%3C%2FSTRONG%3E%20the%20same%20as%20the%20spinlockBackoffs%20presented%20in%20the%20sp_server_diagnostics%20output.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20A%20spinlock%20backoff%20is%20only%20counted%20in%20sp_server_diagnostics%20when%20the%20spinlock%20has%20been%20declared%20%3CSTRONG%3E%20SICK%20%3C%2FSTRONG%3E%20.%26nbsp%3B%26nbsp%3B%20Sick%20is%20the%20term%20used%20to%20indicate%20that%20the%20code%20has%20attempted%20to%20acquire%20the%20spinlock%20ownership%20but%20after%2010%2C000%20%3CEM%3E%20s%20%3C%2FEM%3E%20of%20spins%20and%20lightweight%20backoffs%2C%20for%20approx.%205%20seconds%2C%20ownership%20could%20not%20be%20acquired.%26nbsp%3B%26nbsp%3B%20At%20the%20~5%20second%20point%20the%20code%20performs%20a%20more%20agressive%20sleep%20operation%20because%20the%20spinlock%20appears%20to%20be%20damaged%20or%20hung%20up%20in%20some%20way%2C%20or%20is%20%3CSTRONG%3E%20sick%2C%20%3C%2FSTRONG%3E%20if%20you%20will.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20XEvent%20%3CSTRONG%3E%20spinlock_backoff%20%3C%2FSTRONG%3E%20maps%20to%20the%20dm_os_spinlock_stats%2C%20backoff%20column.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20XEvent%20%3CSTRONG%3E%20spinlock_backoff_warning%20%3C%2FSTRONG%3E%20maps%20to%20the%20sp_server_diagnostics%20output%20and%20is%20produced%20when%20the%20spinlock%20is%20declared%20sick.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20The%20%3CSTRONG%3E%20System%20%3C%2FSTRONG%3E%20component%20can%20report%20%3CSTRONG%3E%20ERROR%20%3C%2FSTRONG%3E%20state%20once%20the%20spinlockBackoffs%20reach%20a%20count%20of%202%20or%20greater%20for%20the%20same%20sickSpinlockType.%26nbsp%3B%20This%20indicates%20that%20for%20approx.%2C%2010%20or%2015%20seconds%20the%20spinlock%20could%20not%20acquire%20ownership%2C%20signaling%20a%20larger%20issue%20on%20the%20system.%26nbsp%3B%20(Orphaned%20spinlock%2C%20CPU%20problems%2C%20etc%E2%80%A6)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%0A%3C%2FSYSTEM%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318475%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2012%2C%202014%20There%20are%20numerous%20articles%20outlining%20how%20spinlocks%20work%20so%20I%20won%E2%80%99t%20cover%20the%20details%20in%20this%20post.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318475%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EHigh%20Availability%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20OS%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Jan 15 2019 04:21 PM
Updated by: