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