Detect SQL Server CPU Pressure

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 TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *