SQL SERVER – Measure CPU Pressure – Detect CPU Pressure

Many a time, DBAs face the following question: can the CPU handle the current transaction? I have seen many DBAs getting confused about whether they should get a CPU with higher clock speed or more number of CPUs when it is time to upgrade the system. As we all know, the CPU (or the Central Processing Unit) is the core of any system. The CPU is responsible for not only SQL Server operations but also all the OS (Operating System) tasks related to the CPU on the server.  Let us learn about how to measure CPU Pressure.

It is quite possible that although we are running very few operations on our SQL Server, we still do not obtain the expected results. This is when the SQL Server CPU has to be checked. Again, please note that I am not suggesting that the CPU is the root cause of poor performance in this case. There are ample chances that the Memory, Input/Output or Tempdb can be responsible for performance degradation. However, to find out the “real” culprit, we must run a test; here, we intend to start running tests on the CPU.

Let us understand the simple meaning of CPU pressure. CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

We can run several different types of queries to test the CPU. However, my personal favorite is the following one. The resultset will contain as many rows as those being used by the CPU SQL Server.

SELECT
scheduler_id,
cpu_id,
current_tasks_count,
runnable_tasks_count,
current_workers_count,
active_workers_count,
work_queue_count,
pending_disk_io_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

Above query will give result something like following.

Before we go ahead, let us familiarize ourselves with the terms. First of all, we should understand what a “scheduler” refers to in the SQL Server. Each SQL Server instance behaves and acts like any OS internally. It has schedules and also it synchronizes concurrent tasks by itself without going to OS. Each instance can handle many concurrent requests. However, there is a point at which the instance cannot accept any more requests. SQL Servers have a dynamic management view (DMV), which in fact keeps a log of all the schedule-related tasks and also it provides a good idea about the CPU pressure.

current_tasks_count is the number of counts of the currently running task. However, we should give the utmost consideration to runnable_tasks_count. If this number is higher, it indicates that a large number of queries, which are assigned to the scheduler for processing, are waiting for its turn to run. This gives a clear indication of the CPU pressure. Additionally, count pending_disk_io_count displays the tasks that are yet to be processed in the scheduler. For better processing, this count is expected not to be very high.

When we say that the numbers are high or low, it does not make any sense unless we compare it to a standard or any other known count. Therefore, here, these numbers are compared to the worker counts (current_worker_count). If current_worker_count is 24 and there are 1000 tasks in queue, then this is not a good scenario. Thus, you can always look at the numbers and make your own judgment here.

Well, in this article, I have done a lot of talking with a very little script. Please do let me know your opinions regarding whether this was simple enough to convey the message regarding CPU pressure.

Reference : Pinal Dave (https://blog.sqlauthority.com)