SQL Server Performance

CPU Performance & Processor Queue Length

Discussion in 'Performance Tuning for DBAs' started by Brad, Oct 28, 2002.

  1. Brad New Member

    I am a student at Virginia Tech and am writing a research paper for a class of mine about database performance issues. I found an article of yours online that was very informative and I was going to use it as a reference, but I have a question that I hope you could answer for me.

    The article I'm referring to is SQL Server Performance Trend Analysis Part 4: Interpreting Performance Monitor Counters.

    My question is from the section about CPU performance. In it you talk about processor queue length and you mention that if the processor queue length exceeds 2 per CPU for continuous periods...you probably have a CPU performance bottleneck. What I'm wondering is what does the 2 represent in the measure? Is is CPU cycles, minutes, etc?
  2. bradmcgehee New Member

    The processor counter, processor queue length, refers to the number of threads that are in the server's processor queue, waiting to be executed by the CPU. All servers, whether they have a single CPU, or multiple CPUs, have only one processor queue. As processes execute on your server, they spawn one or more threads that need to be executed by the CPU or CPUs on the servers.
    The processor queue length is a measurement of the last observed value, and it is not an average of any kind.

    If the CPU or CPUs are not busy when a thread in put into the processor queue, it is immediately executed by a CPU. But if all of the available CPUs are busy executing threads, then incoming threads have to wait in the processor queue until there is a CPU available to process the waiting thread.

    As SQL Server becomes busier, it can send threads to the CPU or CPUs faster than they can be executed, causing the processor queue to fill. As a rule of thumb, if the processor queue exceeds a total of 2 threads, this is a strong indication that the CPU or CPUs in your server have become a bottleneck.

    On occasion, the processor queue length will spike over 2 for short periods of time. This is normal and is not a reason to worry. But if you see a processor queue length of 2 or greater for extended periods of time (say 5 minutes or more) on a regular basis, then most likely your server is experiencing a CPU bottleneck.

    Generally, when evaluating a server for potential CPU bottlenecks, I like to not only watch the processor queue length, I also like to watch the % Processor Time for all of the CPUs in the server. If I see that the processor queue length is often over 2, and I see that the Total % Processor Time is over 80% for all of the CPUs, then I can safely state that the server is experiencing a CPU bottleneck.

  3. sha_jit New Member

    Thanks a lot. I have faced quite same scenario like this.In our production environment our DB server is virtual server with 2 GB RAM and 1 CPU 1.8 gz. Our database backup size is 4.4 GB. In this case I run several counter to check th bottle neck of the performance slow down.
    (W. Europe Standard Time)(-60) Available Bytes Pages/sec PhysicalDisk(_Total)% Disk Time Avg. Disk Queue Length % Processor Time Buffer cache hit ratio User Connections SQL Re-Compilations/sec Processor Queue Length
    Avarage 880155655.9 397.8801259 48.67445686 0.973350826 44.03994985 99.80223927 18.44186047 0.80632564 10.10852713
    Above I copy paste average values of counters what I tracked down during the business hrs. It clearly shows processor Q length is 10 and sql recompilation is 0.80, but on the other hand processor time is 44.03. Here is my query. Is there CPU bottle neck or anything I need to take care before take any decision
    Please advice me


Share This Page