Database Reference
In-Depth Information
Chapter 4
CPU Performance Analysis
This chapter concludes the topic's exploration of the system, with a discussion about CPU, network, and general SQL
Server metrics. The CPU is the work engine of a system and keeps everything running. All the different calculations
required for gathering and delivering data, maintaining the system, and ordering access are performed by the CPU. Getting
bottlenecked on the CPU can be a difficult process to work out of. Unlike memory, which you can sometimes easily install
more of, or disks, which you can sometimes easily add more or upgrade, CPUs are an integral part of the system you're
running on and can frequently be upgraded only by buying newer machines. So, you'll want to keep an eye on CPU usage.
Networks are seldom a major bottleneck for SQL Server, but it's good to keep an eye on them too. Finally, there are some
SQL Server internal processes that you'll need to gather metrics on. This chapter covers the following topics:
How to gather metrics on the processor
•
•
Additional metrics available through T-SQL queries
•
Methods for resolving processor bottlenecks
Processor Bottleneck Analysis
SQL Server makes heavy use of any processor resource available. You can use the Performance Monitor counters in
Table
4-1
to analyze pressure on the processor resource.
Table 4-1.
Performance Monitor Counters to Analyze CPU Pressure
Object(Instance[,InstanceN])
Counter
Description
Value
Processor(_Total)%
Processor Time
Percentage of time processor
was busy
Average value < 80%, but
compare to baseline
% Privileged
Percentage of processor time
spent in privileged mode
Average value < 10%, but
compare to baseline
System
Processor Queue Length
Number of requests
outstanding on the processor
Average value < 2, but
compare to baseline
Context Switches/sec
Rate at which processor is
switched per processor from
one thread to another
Average value < 5,000,
but compare to baseline
SQL Server:SQL Statistics
Batch Requests/sec
SQL command batches
received per second
Based on your standard
workload
SQL Compilations/sec
Number of times SQL is
compiled
Based on your standard
workload
SQL Recompilations/sec
Number of recompiles
