Dear All,
The other day I saw a blog post from Amit regarding version store troubleshooting. I have been involved in similar scenarios of performance issue where the SQL Server box is undergoing CPU pressure – most of the times the Processor – Processor Time counter is choked at 100%. There is one way to detect CPU pressure from within SQL Server by finding out the number of runnable tasks using the following script:
SELECT COUNT(*) AS workers_waiting, t2.Scheduler_id
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND
t1.scheduler_address = t2.scheduler_address AND
t2.scheduler_id < 255
GROUP BY t2.scheduler_id;
The sys.dm_os_workers DMV is very useful here, it gives you one record per worker that’s running on logical CPUs. One of the columns ‘state’ tells you what exactly is the worker doing. Is it SLEEPING, RUNNING or is in RUNNBALE state, meaning, it is waiting for CPU cycles to be freed.
I would like to put up more such DMVs in future posts. Hope this helps.
Regards
Rahul Sharma
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
