Databases Reference
In-Depth Information
Investigating CPU Problems
The availability of CPU cycles to service SQL Server in a timely manner is critical to database server
performance. Coni guration-based CPU bottlenecks may include max degree of parallelism, the cost
threshold of parallelism, and mis-coni gured CPU hyperthreading. Changing from default coni guration
and the optimum setting for each of these coni guration options is scenario dependent, it can be chal-
lenging to be presecriptive and cover all potential scenarios — there are often edge cases and exceptions.
Kernel Mode and Application Mode
It's important to recognize the difference between kernel mode consumption and application mode
consumption because this concept will provide an important and useful indicator when trouble-
shooting. It applies to both CPU and memory consumption.
Kernel mode refers to internal Windows operating system operations whereby the kernel has unre-
stricted access to system hardware, such as the full memory address range, external devices, and so on.
Application mode (also known as user mode) is responsible for everything else, including running
applications such as SQL Server. All user-mode applications access hardware resources through the
executive, which runs in kernel mode. An application requiring disk I/O submits the request through
the kernel-mode executive, which carries out the request and returns the results to the requesting
user-mode process.
CPU Performance Counters
SQL Servers suffering from performance problems caused by high CPU usage is a common perfor-
mance issue. It can be easy to identify the high-consumption Windows process as sqlservr.exe using
Task Manager, but the counters shown in Table 10-3 will provide additional information to assist in
troubleshooting further.
The performance data should be captured for at least a few minutes to ensure the sample is represen-
tative. If there is an intermittent problem or when gathering a baseline, a longer data capture period
will result in more meaningful results.
TABLE 10-3:
Key CPU PerfMon Counters
COUNTER
WHAT TO LOOK FOR
PROBLEM CONDITION
Processor
% Processor Time
Percent of total time the CPUs are busy
servicing productive requests
>80%
Processor
% Privileged Time
Percent of total CPU time spent servic-
ing kernel-mode requests
>30%
Process
% Processor Time
(sqlservr)
Percent of total time SQL Server spent
running on CPU (user mode + privilege
mode)
>80%
Process
% Privilege Time
(sqlservr)
Percent of total time SQL Server was
executing in privilege mode
>30% of % Processor
Time (sqlservr)
Search WWH ::
Custom Search