Question: How Much Work Each Processor (CPU) is Doing in SQL Server?
Answer: To be honest, I have not seen this question asked in any of the interview questions so far. Actually, this question was asked by one of my clients during Comprehensive Database Performance Health Check. Every time whenever he logs into his system, he finds his CPU running very very high even though he had over 56 active processors. What he really wanted to know what exactly is going on behind the scene of his processors. He really wanted to figure out, how busy his servers are and how much work is pending per Processor (CPU)?
Well, let us see how we can do it with a very simple DMV which has the necessary information.
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count FROM sys.dm_os_schedulers WHERE scheduler_id < 255
When you run above command, it gives us necessary information about each of the processor in your system. Let us see the result first from my machine, and I will explain what each of them does.
scheduler_id – This indicates your processor, and it starts from 0 and not 1.
current_tasks_count – This shows some tasks which are waiting for a worker to execute them as well as waiting or running (suspended or runnable) state.
runnable_tasks_count – This number stands for numbers of the worker assigned to this thread. Remember this number indicates how many tasks which are scheduled to run on the queue. Remember if this number is high, it suggests that there are more numbers which are waiting for the resources to be available.
work_queue_count – This number shows how many tasks are in the pending queue and have not yet got chance to get started.
Mostly, if you see a high number of runnable_tasks_count or work_queue_count, you indeed have some bottleneck around resources. There are various things one can do to make sure that bottleneck around resources is removed. If you have any questions about how to resolve this issue, you can either reach out to me or opt for the training here:
Reference: Pinal Dave (https://blog.sqlauthority.com)
