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.

SQL SERVER - Measure CPU Pressure - Detect CPU Pressure cpu-pressure-800x138

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.

SQL SERVER - Measure CPU Pressure - Detect CPU Pressure cpupress

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)

, , , , ,
Previous Post
SQL Server – Understanding Table Hints with Examples
Next Post
SQL SERVER – Get Query Plan Along with Query Text and Execution Count

Related Posts

6 Comments. Leave new

  • >>It is quite possible that although we are running very few operations on our SQL Server, we still do not obtain the expected results.<<

    Trivial point, but the phrase "expected results" has always had a pretty specific meaning for SQL Server and it is not expected performance (as in the quote) but whether the results of a query are correct.

    Reply
  • Nakul Vachhrajani
    August 18, 2009 10:42 pm

    Hello!

    Thank-you for bringing to light such a nice, yet simple means of studying the “CPU pressure”.

    Many-a-times, I have come across quite a few friends and colleagues who always say – “Server performance issue? Bump-up the RAM to double the present amount”. I must add here that we use a lot of virtualization for our development and RND servers, but ultimately, there is a limit and difference in the amount of juice a server can have and what is really necessary.

    Often the case is that a bad query is the root cause of your CPU spiking up for long durations of time. – which ultimately ends up frying the board. That should be the real focus of attention rather than spending thousands on hardware upgrades.

    After all, if the space shuttle can still run on 8086 processors – how much computing power do we really need?

    Another point where most are caught unaware is the difference between “Multi-core CPU” and “Multi-CPU” systems. While the former acts like multiple CPUs, it ultimately is probably one single physical CPU – that would reduce the number of schedulers, and lead to a bottle neck in a BI-like scenario where CPU loads are often high.

    Your article is a step forward in bringing awareness about these subtle differences, and the fact that not everything has the same solution.

    Looking forward to many such articles in the future.

    Reply
  • Hi Pinal,
    thru this I just want to thank you for the useful tips that you keep on giving. I am pretty new as DBA and therefore your explanations of the terms and issues are very helpful to me. Keep up the good work.
    Sam

    Reply
  • Its really help fully to me….thanks pinal…

    Reply
  • Hi Pinal,

    We would like to do some comparison between different processors on different servers.

    Would you have any sample SQL scripts to run a stress test and provide results for comparision?

    Thanks,

    Paurav

    Reply
  • Excellent article indeed; Would like to see a few more examples for CPU pressure analysis if possible.
    Thanks.

    Reply

Leave a Reply

Menu