High CPU Diagnostic Path

performance critical

#cpu#performance#scheduling#diagnostic

When SQL Server consumes excessive CPU, the root cause typically falls into one of three categories: expensive query compilation/recompilation, inefficient execution plans with scans or excessive row operations, or signal waits from OS-level scheduling pressure.

Overview

High CPU utilization in SQL Server is most often caused by:

Plan cache churn — Frequent ad-hoc queries triggering repeated compilation
Inefficient queries — Missing index scans, key lookups, or sort spills
Parallelism issues — CX_PACKET waits indicating skewed parallel distribution
Signal waits — Threads ready to run but waiting for scheduler time (SOS_SCHEDULER_YIELD)

Diagnostic Steps

1. Identify top CPU-consuming queries

Query sys.dm_exec_query_stats to find queries with the highest total CPU time aggregated over cached plans. Filter by total_worker_time descending, and cross-reference with sys.dm_exec_requests for currently running queries.

2. Check scheduler pressure

Query sys.dm_os_schedulers to view runnable task counts and scheduler load. A consistently high runnable_tasks_count with associated SOS_SCHEDULER_YIELD waits indicates CPU pressure.

3. Examine wait statistics

Review SOS_SCHEDULER_YIELD waits — normal at moderate levels but concerning when consistently in top wait types. Check CXPACKET for parallel query skew and CMEMTHREAD for memory object contention.

4. Review execution plan cache

Use sys.dm_exec_query_stats to find plans with high plan_generation_num indicating frequent recompilations. Cross-reference with total_worker_time and execution_count to calculate average CPU per execution.

Key Scripts

View top 10 high CPU utilization queries — Quick-hit diagnostic for finding current CPU-heavy queries
View CPU utilization history — Historical trend analysis using DMV snapshots
View tasks, threads, and schedulers — Deep dive into scheduler-level activity