SQL Server Slow Under Load: Check CPU and Waits

This diagnostic path is part of our SQL Server performance guide. If you are not sure yet which path fits your problem, start there.

The first question to answer is: What changes between the quiet period and the slow period: the number of calls, the cost per call, or the waiting time?

Step 1: Compare a quiet period with a slow period

Choose two time windows of equal length that you can compare using existing data. Note the start, the end, and the affected tasks. Use the same time base in the application, the operating system, and SQL Server.

Record the following for both windows:

The number of logged-in users describes the actual load only poorly. Ten users can trigger many queries; a hundred logged-in users may be doing nothing at the moment.

Example finding: During the peak, more save requests arrive, while the number of successful completions barely rises. At the same time, response times and the application's queue grow. That points to a backlog. The next steps have to show which resource or conflict causes it.

Also check the workload mix: a minute with many short search queries is a different comparison case than a minute with several large reports.

Step 2: Put high CPU usage in context

First look at the existing monitoring of the operating system or, on a Windows server, at Task Manager: Does the CPU usage of the sqlservr.exe process rise during the affected period? Or is another process consuming the machine?

Look at the trend over the whole time window. A brief spike does not explain a disruption that lasts for minutes. In addition, the average utilization of the whole machine can hide the fact that individual logical processors are heavily loaded. On virtual machines, the allocated and actually available CPU resources are part of the finding. The Microsoft guide to high CPU usage also describes this starting point.

Keep three measures apart:

A long response time therefore does not prove a CPU bottleneck. And a faster query does not automatically use less CPU.

Our article More Cores, Faster Cores or a Better Query? uses lab measurements to show whether more cores or a better query helps under real CPU load.

Step 3: Tell more calls apart from costlier calls

If Query Store covered the period, open the Query Store reports in SQL Server Management Studio under the affected database. Start with Top Resource Consuming Queries, choose CPU time, and set the matching time window. Check whether the view shows total consumption or the average per execution. The same analysis is provided by our query “Most expensive queries in a time window”; run it for both time windows and place the results side by side. The Microsoft overview of Query Store explains the reports.

For queries that stand out, compare the number of calls, CPU time per execution, duration, and plan. If parameters or result sets differ, compare cases that are as similar as possible. SQL statements and complete application operations are different units of counting.

• More calls at a similar cost per call: The existing work is requested more often. Check load spikes, additional reports, retries, or an increase in application calls.
• A similar number of calls at a higher cost per call: Check data volume, parameters, and the execution plan. The diagnostic path “SQL Server Is Sometimes Fast and Sometimes Slow” continues from here.
• Similar CPU time per call but a longer duration: Additionally examine waits and how quickly the application fetches the results. The CPU figure alone does not explain the delay.

If the matching history is missing, record the gap and use existing application or monitoring data for the next comparison.

CPU lab: Finished sooner, more compute

In a lab of our own, we compared a compute-intensive aggregation query over 300,000 rows. It returns one result row and ran once serially and once in parallel with a degree of parallelism (DOP) of four (DOP 4).

The duration dropped by about 69 percent, while the CPU work rose by about 19 percent. Four workers compute at the same time: the query finishes earlier even though more compute time is used in total. Whether that still helps under many concurrent calls only a load comparison like the one below can show.

In the same lab, a query that returned 300,000 rows to the client stayed serial despite MAXDOP 4. Its client duration was about 2.18 seconds, and its CPU time in SQL Server was about 0.20 seconds. So SQL Server did not spend most of the time computing; most of it went into delivering the results to the client and processing them there. In wait statistics, this typically shows up as ASYNC_NETWORK_IO (see Step 4).

MAXDOP limits the permitted degree of parallelism; the setting does not force a parallel execution plan. Therefore check the plan that is actually used. See the Microsoft documentation on MAXDOP.

Step 4: Examine waits in the affected time window

Wait statistics show what SQL Server's workers were waiting for. A worker is an executing thread of work. Waiting is part of normal operation; what matters is which wait matches the observed delay.

sys.dm_os_wait_stats counts all completed waits since the instance started. For a time window you therefore need two snapshots and their difference. You do not have to reset the counters to do this. Two things distort the first look:

• Harmless background waits. Many system tasks spend most of their time waiting for work, for example SLEEP_TASK, LAZYWRITER_SLEEP, XE_TIMER_EVENT, or BROKER_…. Unfiltered, they sit at the very top even though they have nothing to do with your problem.
• Signal wait time. signal_wait_time_ms is the part during which a worker could actually continue but is waiting for a free CPU core. A high share alone does not yet prove CPU pressure: with pure compute work, almost every wait is signal wait, in our lab already at 26 percent CPU utilization. What tells you more is signal wait per second relative to the number of cores, together with CPU utilization and the queue per scheduler (runnable_tasks_count).

A restart sets these counters back to zero, along with the statistics in the plan cache. If the load makes you consider a restart, first read Why does restarting SQL Server help, and why only briefly? and save the snapshot.

The following query measures one window, filters out the background waits, and shows both values. Start it while the problem is occurring:

/* PSG Stack · SQL Server Performance Guide · Query 3
   Wait times in a time window: the difference between two snapshots.

   - Read-only. Resets no counters.
   - Runs with MAXDOP 1 so the diagnostic does not put additional
     parallel load on the server.
   - The window runs via WAITFOR; the query stays busy for that long.
     Start it while the problem is occurring.
   - Permissions: VIEW SERVER STATE
     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).
   - SQL Server 2016 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
   - Harmless background waiting is filtered out. The list is not
     complete; look up unknown wait types in the documentation.
   - Wait times of many sessions overlap. The sum can be larger
     than the length of the window.
*/
DECLARE @window char(8) = '00:05:00';   -- length of the measurement window (hh:mm:ss)

IF OBJECT_ID('tempdb..#before') IS NOT NULL DROP TABLE #before;

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #before
FROM sys.dm_os_wait_stats
OPTION (MAXDOP 1);

WAITFOR DELAY @window;

WITH delta AS (
    SELECT n.wait_type,
           n.waiting_tasks_count - v.waiting_tasks_count AS wait_count,
           n.wait_time_ms        - v.wait_time_ms        AS wait_ms,
           n.signal_wait_time_ms - v.signal_wait_time_ms AS signal_ms
    FROM sys.dm_os_wait_stats AS n
    JOIN #before AS v ON v.wait_type = n.wait_type
    WHERE n.wait_time_ms - v.wait_time_ms > 0
      AND n.wait_type NOT LIKE N'SLEEP[_]%'
      AND n.wait_type NOT LIKE N'BROKER[_]%'
      AND n.wait_type NOT LIKE N'XE[_]%'
      AND n.wait_type NOT LIKE N'QDS[_]%'
      AND n.wait_type NOT LIKE N'HADR[_]%'
      AND n.wait_type NOT LIKE N'PARALLEL[_]REDO[_]%'
      AND n.wait_type NOT IN (
          N'AZURE_IMDS_VERSIONS', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
          N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
          N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL',
          N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT',
          N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'KSOURCE_WAKEUP',
          N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
          N'ONDEMAND_TASK_QUEUE', N'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
          N'PREEMPTIVE_XE_GETTARGETSTATE', N'PVS_PREALLOCATE',
          N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
          N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', N'REDO_THREAD_PENDING_WORK',
          N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESERVED_MEMORY_ALLOCATION_EXT', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK',
          N'SNI_HTTP_ACCEPT', N'SOS_WORK_DISPATCHER', N'SP_SERVER_DIAGNOSTICS_SLEEP',
          N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
          N'SQLTRACE_WAIT_ENTRIES', N'VDI_CLIENT_OTHER', N'WAIT_FOR_RESULTS',
          N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_CKPT_CLOSE',
          N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_RECOVERY')
)
SELECT TOP (15)
    wait_type,
    wait_count,
    CAST(wait_ms / 1000.0 AS decimal(18,1))                         AS wait_s,
    CAST(signal_ms / 1000.0 AS decimal(18,1))                       AS signal_s,  -- ready to run, but no free core
    CAST(100.0 * wait_ms / NULLIF(SUM(wait_ms) OVER (), 0)
         AS decimal(5,1))                                           AS share_pct,
    CAST(1.0 * wait_ms / NULLIF(wait_count, 0)
         AS decimal(18,1))                                          AS ms_per_wait
FROM delta
ORDER BY wait_ms DESC
OPTION (MAXDOP 1);

Example output from our lab (SQL Server 2022, 25-second window, shortened). During the window, 20,000 individual inserts ran along with a block of about ten seconds:

wait_type      wait_count wait_s signal_s share_pct ms_per_wait
LCK_M_X        1          9.9    .0       81.8      9887.0
WRITELOG       3206       2.2    .3       18.1      .7

LCK_M_X is a single long wait: the block. WRITELOG consists of many short waits, because every single insert waits for the transaction log to be written. On a quiet server, the top entries are often just 0.0 seconds, such as PREEMPTIVE_OS_…; you can ignore those.

The Microsoft documentation on wait statistics explains the individual wait types. For a single session, there is sys.dm_exec_session_wait_stats.

Starting with SQL Server 2017, Query Store also captures waits per query. The Query Wait Statistics report then helps you find the affected queries for a wait category. There, several wait types are grouped into categories. The CPU category means scheduler waits, that is, “ready, but no free core”; check the measured compute time separately. The Microsoft mapping of wait types explains these categories.

Use standout values as pointers:

A high entry in a wait list does not by itself call for a configuration change. Parallelism waits in particular need the context of the specific execution.

Waits lab: 24 seconds of waiting in three seconds of real time

In a test of our own, eight separate sessions each waited three seconds at the same time using WAITFOR.

The eight waits overlap. Together they add up to about 24 seconds, even though only about three seconds pass on the clock. The test deliberately creates waiting and does not demonstrate a bottleneck.

The same applies when reading wait statistics: the waits of many sessions or parallel workers overlap, so the sum can be larger than the elapsed time. Do not add CPU time and summed wait times together into a supposed response time.

Wait-sum lab: More waiting, yet faster

What this means for judging a change is shown by the aggregation report from our mixed-load lab (see Step 5). For each setting, Query Store captured 87 executions of the report: duration, CPU time, and wait time, each averaged per execution.

With MAXDOP 8, the report was about 78 percent faster than serial, even though its summed wait time rose from 0.19 to 13.24 seconds. Query Store assigns almost all of it to the Parallelism category (12.92 seconds): the parallel workers wait for each other at the same time, and every one of these waits is counted. Anyone looking only at the wait sum would have rated both parallel settings worse than the serial one, even though they were clearly faster. And from MAXDOP 4 to 8, the sum rose further while the duration kept falling. In SQL Server Management Studio, the Query Store report Query Wait Statistics shows these values per query (SQL Server 2017 and later).

Waits explain how an execution proceeds. Whether a change helps is shown by the duration of the affected task and by the rest of the workload under real load. Step 5 looks at the rest of the workload using client measurements from the same lab: there, the short application transactions became slower with MAXDOP 8; the test did not isolate the exact cause.

Step 5: Test changes against the rest of the workload

Before making a change, state the expected effect: for example, less CPU work for the same search, or shorter waits for the save requests. Afterward, compare under similar load and with the same workload mix.

Besides the optimized query, also check successfully completed operations, errors, timeouts, and the response times of other tasks. A change can help one report and at the same time delay the short application operations.

Mixed-load lab: Faster reports, slower application transactions

In another lab of our own, we set a fixed offered load: 800 short application transactions per second plus two report types, together one report per second. One report type aggregates over four tables and does a lot of computation; the other sorts. Two of ten measured settings show the trade-off:

p99 is the threshold below which 99 percent of the measured individual calls fell; p95 is the same for 95 percent. For the reports we use p95 because there are far fewer measurements of them than of the short transactions. The values come from the individual client measurements, not from Query Store averages. The Cost Threshold for Parallelism is the same in both rows; the test series varied it in other settings.

With MAXDOP 8, both report types were faster, while the slowest short transactions were slower. The difference was repeatable: across the individual runs, p99 of the short transactions was between about 25 and 32 ms at MAXDOP 4 and between about 46 and 59 ms at MAXDOP 8.

What did not happen is noteworthy: SQL Server's CPU utilization was only about 35 percent of the eight CPUs in both settings and did not rise with MAXDOP 8. A CPU bottleneck therefore does not explain the increase; the test did not isolate exactly what the short transactions waited for. In addition, all ten settings met the targets set in advance (p99 of the short transactions at most 100 ms, p95 of the reports at most 5 seconds). Which trade-off is right therefore depends on the requirements for both tasks, not on a universally best value.

Record the result: Which explanation does the finding support?

A useful interim result connects observation, evidence, and the next cross-check. For the mixed-load lab, it could read like this:

At the same offered load, MAXDOP 8 lowered the p95 response time of the aggregation report from 858 to 510 ms. At the same time, the p99 response time of the short application transactions rose from 30 to 53 ms, although CPU utilization did not rise. Next we will check which setting meets the agreed response time targets of both tasks under the expected workload mix, and we will examine the waits of the short transactions.

For your own SQL Server, add the time period, the load volume, the affected queries, and the measurement source. If more work is arriving, check capacity and concurrency. If individual calls are getting more expensive, examine their plans and data access. If operations mostly wait, follow the relevant resource or the blocking chain. That way, the finding leads to a verifiable next action.

More diagnostic paths