Monitoring CPU Use

You should periodically monitor the computer running Microsoft® SQL Server™ to determine if CPU utilization rates are within normal ranges. A continually high CPU rate may indicate the need for a CPU upgrade or the addition of multiple processors. Additionally, a continually high CPU utilization may indicate a poorly tuned or designed application. Optimizing the application can lower CPU utilization. For more information, see Application Design.

A good way to determine this is to use the Processor:% Processor Time counter. This counter in Windows NT Performance Monitor monitors the amount of time the CPU spends processing a nonidle thread. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. For multiprocessor systems, a separate instance of this counter should be monitored for each processor. This value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.

Optionally, you can also monitor:

• Processor: % Privileged Time

  This counter corresponds to the percentage of time the processor is spending executing Microsoft Windows NT® kernel commands such as processing SQL Server I/O requests. If this counter is consistently high in conjunction with the Physical Disk counters, then SQL Server could be I/O-bound. Consider a faster or more efficient disk subsystem.

  Note Different disk controllers and drivers use different amounts of kernel processing time. Efficient controllers and drivers use less privileged time, leaving more processing time available for user applications, increasing overall throughput.

  • Processor: %User Time

    This counter corresponds to the percentage of time the processor is spending executing user processes such as SQL Server.

  • System: Processor Queue Length

    This counter corresponds to the number of threads waiting for processor time. A processor bottleneck develops when threads of a process require more processor cycles than are available. If more than a few program processes are contending for most of the processor’s time, you might need to install a faster processor or an additional processor, if you are using a multiprocessor system.

  When you examine processor usage, consider the type of work the computer running SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time can be used. If this causes the performance of other applications to suffer, try changing the workload, for example, by dedicating the computer to running SQL Server.

  Values around 100 percent, where many client requests are executing, may indicate that processes are queuing up, waiting for processor time, and causing a bottleneck. Resolve the problem by adding more powerful processors.