A PFE colleague from the UK approached me yesterday with the following concern:
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 (
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.
Great! When baselining spinlock behavior in SQL Server, I was wondering if it made sense to capture spins, collisions, backoffs AND sleep_time for the various spin types. Since each thread will only sleep every 8th backoff - I'll keep gathering all 4 of those stats.