How do you explain that sys.dm_os_spinlock_stats.backoffs keeps increasing and no sqlos.spinlock_backoff event is produced?

  • Article

A PFE colleague from the UK approached me yesterday with the following concern:

Hi Nacho,

I may need to find out the call stacks when a spinlock backoff occurs at a customer tomorrow.

So I found this script from a SQLCAT article: http://sqlcat.com/sqlcat/b/msdnmirror/archive/2010/05/11/resolving-dtc-related-waits-and-tuning-scalability-of-dtc.aspx

create event session lock_hash_spin on server

add event sqlos.spinlock_backoff (action (package0.callstack)

where type=61) -- 61 == LOCK_HASH

add target package0.asynchronous_bucketizer (

set filtering_event_name='sqlos.spinlock_backoff',

source_type=1, source='package0.callstack')

with (MAX_MEMORY=256MB, MEMORY_PARTITION_MODE = PER_CPU)

I have a workload where I can generate some lock_hash spinlock backoffs, but every time I try to repro this I get 0 results returned. I know backoff are occurring as I am checking this using:

select * from sys.dm_os_spinlock_stats order by backoffs desc

What I expect is that every time I get a backoff the LOCK_HASH I want to see the call stack that was part of this. I have even tried removing the predicate,’ where type=61’ but I still get nothing, am I doing something really stupid?

Well, he wasn’t doing anything stupid at all. He’s a smart guy and rarely do such things.

The only thing he was missing was that the backoffs exposed via sys.dm_os_spinlock_stats are incremented every time the worker backoffs because an attempt to immediately (i.e. with no wait) acquire a spinlock of that type fails.

However, whether the sqlos.spinlock_backoff event needs to be raised or not is only evaluated inside a function that gets called when the worker decides to sleep as a consequence of the backoff. That happens every eighth (8th) backoff occurrence. During the remaining seven attempts, the worker only calls SwitchToThread API.

Therefore we can say that for the aforementioned XEvent to be produced, a more serious contention on a given spinlock must be occurring.