SQL Server - Identify CPU Pressure

In this blog, we will try to measure the CPU pressure, many tools are available in the market to check the health and performance of the SQL Server. As we know, the CPU is the core part of any system which is responsible for all the operations performed in the system, whether it is an operating system related task or SQL Server.

CPU may not be the root cause of the poor performance; there are lots of things which may be responsible for a poor performance like Memory, Input/output or Tempdb, etc. To get real fact we must run a test, here we will start to run a check on the CPU.

Now we will check the query for CPU pressure. 

The query for Average CPU Load

SELECT COUNT(*) Schedulers, AVG(runnable_tasks_count) AS AvgRunnableTaskCount, AVG(current_tasks_count) AS AvgCurrentTaskCount, AVG(pending_disk_io_count) AS AvgPendingDiskIOCount, AVG(work_queue_count) AS AvgWorkQueueCount, AVG(active_workers_count) AS AvgActiveWorkerCount, AVG(current_workers_count) AS AvgCurrentWorkerCount FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255;

The query for Total CPU Load

SELECT COUNT(*) Schedulers, SUM(runnable_tasks_count) AS SumRunnableTaskCount, SUM(current_tasks_count) AS SumCurrentTaskCount, SUM(pending_disk_io_count) AS SumPendingDiskIOCount, SUM(work_queue_count) AS SumWorkQueueCount, SUM(active_workers_count) AS SumActiveWorkerCount, SUM(current_workers_count) AS SumCurrentWorkerCount FROM sys.dm_os_schedulers WITH (NOLOCK) WHERE scheduler_id < 255;

Now we will do the analysis of the queries.

current_tasks_count is the count of the currently running task.

runnable_tasks_count is the count of the task which can smoothly handle by the CPU. If this is less than current_tasks_count, it means the large number of queries, which are assigned to the scheduler for processing, are waiting for its turn to run.

pending_disk_io_count displays the tasks that are yet to be processed in the scheduler.

work_queue_count is the number of tasks in the pending queue. These tasks are waiting for a worker to pick them up.

active_workers_count represents all workers that have associated tasks and are running under non-preemptive mode. Some tasks, such as network listeners, run under preemptive scheduling.

current_workers_count is the number of workers that are associated with this scheduler. This count includes workers that are not assigned any task.