Can you tell me more about the processor queue length?

Question

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?

Answer

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.