Professional SQL Server® 2005 Performance Tuning

3.5. CPU Bottlenecks

If you find yourself in a situation where the process sqlservr.exe is consuming the lion's share of your CPU cycles, you'll already have a good idea that something has gone wrong within SQL Server. Typically you'll hear problem reports from users who may be experiencing poor performance or connection timeouts. You might be in a position where you've run Task Manager and perhaps even System Monitor and you've observed excessive CPU utilization that is caused by SQL Server. Now you'll need to determine the cause of this CPU demand within SQL Server to try to get a better understanding of the cause of the CPU activity. As with most resource shortages, high CPU conditions could be triggered by mis-configuration of the hardware or operating system or could be related to problems with the database schema.

3.5.1. Confirming CPU Bottlenecks

In a situation where you suspect a CPU bottleneck, it can be fairly straightforward to confirm this with the following counters in System Monitor:

• System — Processor Queue Length: If your processors are constantly busy, you'll expect to see a queue building. Any queue for processors has the potential to impact user experience. Therefore, monitor queue length closely as any consistent queue will inevitably impact overall performance. If queue length is averaging more than three, then consider this a cause for concern.

• Processor — %Privilege Time: Privilege Time is time spent by the CPU servicing kernel mode activities — that is ...