Database Reference
In-Depth Information
Sys.dm_os_workers and Sys.dm_os_schedulers
These DMOs display the worker and scheduler threads within the Windows operating system. Running queries
against these regularly will allow you to get counts of the number of processes that are in a runnable state. This is an
excellent indication of processor load.
Processor Bottleneck Resolutions
A few of the common processor bottleneck resolutions are as follows:
•
Optimizing application workload
•
Eliminating or reducing excessive compiles/recompiles
•
Using more or faster processors
•
Not running unnecessary software
Let's consider each of these resolutions in turn.
Optimizing Application Workload
To identify the processor-intensive queries, capture all the SQL queries using Extended Events sessions (which I will
discuss in the next chapter) and then group the output on the
CPU
column. The queries with the highest amount
of CPU time contribute the most to the CPU stress. You should then analyze and optimize those queries to reduce
stress on the CPU. Frequently, the cause for CPU stress is not extensive calculations within the queries but actually
contention within logical I/O. Addressing I/O issues can often help you resolve CPU issues as well. You can also
query directly against the sys.dm_exec_query_stats or sys.dm_exec_procedure_stats dynamic management view
to see immediate issues in real time. Finally, using both a query hash and a query plan hash, you can identify and
tune common queries or common execution plans (this is discussed in detail in Chapter 14). Most of the rest of the
chapters in this topic are concerned with optimizing application workload.
Eliminating Excessive Compiles/Recompiles
A certain number of query compiles and recompiles is simply to be expected, especially, as already noted, when
working with ORM tools. It's when there is a large number of these over sustained periods that a problem exists. It's
also worth noting the ratio between them. Having a high number of compiles and a low number of recompiles means
that few queries are being reused within the system (query reuse is covered in detail in Chapter 9). A high number of
recompiles will cause high processor use. Methods for addressing recompiles are covered in Chapter 17.
Using More or Faster Processors
One of the easiest resolutions, and one that you will adopt most of the time, is to increase system processing
power. However, because of the high cost involved in a processor upgrade, you should first optimize CPU-intensive
operations as much as possible.
The system's processing power can be increased by increasing the power of individual processors or by adding
more processors. When you have a high % Processor Time counter and a low Processor Queue Length counter, it
makes sense to increase the power of individual processors. In the case of both a high % Processor Time counter and
a high Processor Queue Length counter, you should consider adding more processors. Increasing the number of
processors allows the system to execute more requests simultaneously.
