System Troubleshooting - Pro SQL Server Internals - page 528

wait times in the sys.dm_os_wait_stats view, as shown in Listing 27-9. Signal waits indicate the waiting times for the
CPU, while resource waits indicate the waiting times for resources, such as for pages from disk. Although Microsoft
recommends that the signal wait type should not exceed 25 percent, I believe that 15-20 percent is a better target on
busy systems.

Listing 27-9. Comparing signal and resource waits
select
sum(signal_wait_time_ms) as [Signal Wait Time (ms)]
,convert(decimal(7,4), 100.0 * sum(signal_wait_time_ms) /
sum (wait_time_ms)) as [% Signal waits]
,sum(wait_time_ms - signal_wait_time_ms) as [Resource Wait Time (ms)]
,convert(decimal(7,4), 100.0 * sum(wait_time_ms - signal_wait_time_ms) /
sum (wait_time_ms)) as [% Resource waits]
from
sys.dm_os_wait_stats with (nolock)
option (recompile)

Plenty of factors can contribute to CPU load in a system, and bad T-SQL code is at the top of the list. Imperative
processing, cursors, XQuery, multi-statement user-defined functions and complex calculations are especially
CPU-intensive.
The process of detecting the most CPU-intensive queries is very similar to that for detecting non-optimized
queries. You can use the sys.dm_exec_query_stats view, as was shown in Listing 27-5. You can sort the data
by the total_worker_time column, which detects the most CPU-intensive queries with plans currently cached.
Alternatively, you can use SQL Trace and Extended Events, filtering data by CPU time rather than by I/O metrics.

■ both extended events and especially SQl trace introduce additional overhead on the server and are not always
the best option if Cpu load is very high. at a bare minimum, avoid SQl trace and use extended events if this is the case.

Note

Constant recompilation is another source of CPU load. You can check the Batch Requests/Sec , SQL Compilations/Sec,
and SQL Recompilations/Sec performance counters and calculate plan reuse with the following formula:

Plan Reuse = (Batch Requests/Sec - (SQL Compilations/Sec - SQL Recompilations/Sec)) / Batch
Requests/Sec

Low plan reuse in OLTP systems indicates heavy Ad-Hoc activity and often requires code refactoring and
parameterization of queries. However, non-optimized queries are still the major contributor to CPU load. With
non-optimized queries, SQL Server processes a large amount of data, which burns CPU cycles regardless of other
factors. In most cases, query optimization reduces the CPU load in the system.
Obviously, the same is true for bad T-SQL code. You should reduce the amount of imperative data processing,
avoid multi-statement functions, and move calculations and XML processing to the application side if at all possible.

Parallelism
Parallelism is perhaps one of the most confusing aspects of troubleshooting. It exposes itself with the CXPACKET wait
type, which often can be seen in the list of top waits in the system. The CXPACKET wait type, which stands for Class
eXchange , occurs when parallel threads are waiting for other threads to complete their execution.