Which Processes are waiting on a Spinlock?

Spinlock contention is always a real headache to deal with. I recently saw an issue when spinlock contention on SOS_CACHESTORE was making the server virtually unresponsive. The issue was very intermittent with no obvious pattern but the assumption is that it was caused by a particular process in the application. Finding what that process was the tricky part, they don’t show up as waiting tasks so your usual scripts for looking for waiting processes may not work here.

The place that we can see works who are waiting on a spinlock is in sys.dm_os_workers, specifically the is_sick column. If that’s 1 then the worker is stuck trying to get access to a spinlock.

This is just a very quick post to share the little script that I wrote to return all workers are waiting on a spinlock. Hopefully it might be useful for anyone who gets a similar problem and wants to know what processes are being held up by spinlocks.

SELECT workers.is_sick, requests.session_id, db_name(requests.database_id),  requests.status, inputbuffer.event_info
FROM sys.dm_os_workers workers
JOIN sys.dm_exec_requests requests ON workers.task_address = requests.task_address
OUTER APPLY sys.dm_exec_input_buffer(requests.session_id,requests.request_id) inputbuffer
WHERE workers.is_sick = 1

Thanks for reading

2 thoughts on “Which Processes are waiting on a Spinlock?

Add yours

    1. Hi Chris, diagnosing spin lock contention in the first place is a big and tricky subject. The first sign that you’re likely to notice will be very high CPU consumption by SQL with no obvious reason in the processes that are running. You can use sys.dm_os_spinlocks to help identify an issue if you can see high spins and backoffs. That DMV is one of those ones that are cumulative since the start of the server so you’ll probably want to take the difference between two runs. There’s quite a good Microsoft whitepaper if you wanted to look into it some more. https://docs.microsoft.com/en-us/sql/relational-databases/diagnose-resolve-spinlock-contention?view=sql-server-ver15

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: