Database Reference
In-Depth Information
Let's discuss these counters in more detail.
% Processor Time
% Processor Time should not be consistently high (greater than 80 percent). The effect of any sustained processor
time greater than 90 percent is effectively the same as that of 100 percent. If % Processor Time is consistently high
and disk and network counter values are low, your first priority must be to reduce the stress on the processor. Just
remember that the numbers here are simply suggestions; people can disagree with these numbers for valid reasons.
Use them as a starting point for evaluating your system, not as a specific recommendation.
For example, if % Processor Time is 85 percent and you are seeing excessive disk use by monitoring I/O counters,
it is quite likely that a major part of the processor time is spent on managing the disk activities. This will be reflected in
the % Privileged Time counter of the processor, as explained in the next section. In that case, it will be advantageous
to optimize the disk bottleneck first. Further, remember that the disk bottleneck in turn can be because of a memory
bottleneck, as explained earlier in the chapter.
You can track processor time as an aggregate of all the processors on the machine, or you can track the percentage
utilization individually to particular processors. This allows you to segregate the data collection in the event that SQL
Server runs on three processors of a four-processor machine. Remember, you might be seeing one processor maxed out
while another processor has little load. The average value wouldn't reflect reality in that case. Use the average value as
just an indicator and the individual values as more of a measure of actual load and processing on the system.
% Privileged Time
Processing on a Windows server is done in two modes:
user mode
and
privileged
(or
kernel
) mode. All system-level
activities, including disk access, are done in privileged mode. If you find that % Privileged Time on a dedicated SQL
Server system is 20 to 25 percent or more, then the system is probably doing a lot of external processing. It could be I/O,
a filter driver such as encryption services, defective I/O components, or even out-of-date drivers. The % Privileged Time
counter on a dedicated SQL Server system should be at most 5 to 10 percent, but use your baseline to establish what
looks like normal behavior on your systems.
Processor Queue Length
Processor Queue Length is the number of threads in the processor queue. (There is a single processor queue, even on
computers with multiple processors.) Unlike the disk counters, the Processor Queue Length counter does not read threads
that are already running. On systems with lower CPU utilization, the Processor Queue Length counter is typically 0 or 1.
A sustained Processor Queue Length counter of greater than 2 generally indicates processor congestion. Because
of multiple processors, you may need to take into account the number of schedulers dealing with the processor
queue length. A processor queue length more than two times the number of schedulers (usually 1:1 with processors)
can also indicate a processor bottleneck. Although a high % Processor Time counter indicates a busy processor, a
sustained high Processor Queue Length counter is a more certain indicator. If the recommended value is exceeded,
this generally indicates that there are more threads ready to run than the current number of processors can service in
an optimal way.
Context Switches/Sec
The Context Switches/sec counter monitors the combined rate at which all processors on the computer are switched
from one thread to another. A context switch occurs when a running thread voluntarily relinquishes the processor, is
preempted by a higher-priority ready thread, or switches between user mode and privileged mode to use an executive
or a subsystem service. It is the sum of Thread:Context Switches/sec for all threads running on all processors in the
computer, and it is measured in numbers of switches.
