SQL SERVER – Measure CPU Pressure – CPU Business

Many a time, DBAs face the following question: can the CPU handle the current transaction? I have seen many DBAs getting confused on whether they should get a CPU with higher clock speed or more number of CPUs when it is time to upgrade 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.

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 dynamic management view (DMV), which in fact keeps a log of all the scheduler-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 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 judgement here.

Well, in this article, I have done a lot of talking with 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 (http://blog.SQLAuthority.com)

About these ads

7 thoughts on “SQL SERVER – Measure CPU Pressure – CPU Business

  1. >>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.

  2. 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.

  3. 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

  4. 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

  5. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s