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

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 )

Google photo

You are commenting using your Google 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: