Measure SQL Server waits over the incident interval

A server's largest lifetime wait is not necessarily the reason an application became slow at 10:15. The total may include weeks of maintenance, idle background work, and a workload that no longer exists. To investigate a short incident, measure what changed during that interval and then connect the change to active requests.

Subtract observations without resetting the server

The script captures two snapshots ten seconds apart in one session and subtracts cumulative counters. It does not clear shared statistics. Run it with the server diagnostic permissions appropriate to your SQL Server version: generally VIEW SERVER STATE before 2022 and VIEW SERVER PERFORMANCE STATE from 2022 onward.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #WaitBefore
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT
    w.wait_type,
    w.waiting_tasks_count - b.waiting_tasks_count AS waits_started,
    w.wait_time_ms - b.wait_time_ms AS wait_ms,
    w.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms,
    (w.wait_time_ms - b.wait_time_ms)
      - (w.signal_wait_time_ms - b.signal_wait_time_ms) AS resource_ms
INTO #WaitDelta
FROM sys.dm_os_wait_stats AS w
JOIN #WaitBefore AS b ON b.wait_type = w.wait_type;

IF EXISTS
(
    SELECT 1 FROM #WaitDelta
    WHERE waits_started < 0 OR wait_ms < 0 OR signal_ms < 0
)
    THROW 50001, 'Counters changed incompatibly; discard this sample.', 1;

SELECT TOP (20) *
FROM #WaitDelta
WHERE wait_ms > 0
ORDER BY wait_ms DESC;

DROP TABLE #WaitDelta;
DROP TABLE #WaitBefore;

The WAITFOR is simply the sampling interval. It contributes its own wait, so this demonstration deliberately leaves every wait type visible rather than presenting a magical exclusion list. In a monitoring collector, store UTC sample times, server identity, and engine start time with each snapshot. A persistent collector normally takes separate scheduled samples instead of occupying a session with WAITFOR.

Negative differences are evidence that the snapshots cannot be compared, commonly because someone cleared counters. A restart normally also breaks this temporary-table session, while a persistent collector must explicitly detect the new engine start time. The simple check cannot detect every reset: a counter cleared and then grown past its previous value can still produce a positive delta. Coordinate counter-clearing practices and retain collection context.

Do not subtract max_wait_time_ms to obtain an interval maximum. A lifetime maximum is not an additive counter. If the first snapshot's maximum is 20 seconds and the second is also 20 seconds, a new 19-second wait is invisible to that subtraction.

Interpret the units before ranking causes

The wait-time total includes signal time. Subtracting signal time gives the resource-wait component represented by those counters. Signal time describes the delay between being ready to run and actually running, so a high value deserves correlation with runnable work and CPU demand. It is not a complete CPU diagnosis by itself.

Accumulated wait milliseconds are worker time, not wall-clock time. Ten tasks each waiting one second can contribute approximately ten seconds during a one-second interval. Parallel execution and concurrent requests therefore allow totals larger than the collection window. Dividing the total by elapsed milliseconds does not produce a conventional utilization percentage.

Wait counts and wait durations also cross sample boundaries differently. The count advances when a wait begins, while completed wait durations are reflected later. An average computed from a short interval's duration delta divided by count delta can be misleading or undefined. Use longer observations for broad trends and individual event evidence when a precise distribution matters.

Some waits describe normal background coordination. Filtering them can make a dashboard readable, but keep the raw observations and document the filter. A wait removed for one investigation may matter in another. Percentages also depend on that denominator: removing one category changes every remaining share without changing actual work.

Follow a wait to the responsible workload

An increase in lock waits suggests finding blockers, transaction ages, and the affected objects. PAGEIOLATCH waits lead toward page reads and the amount of I/O requested, while PAGELATCH concerns in-memory synchronization and should not automatically trigger a storage purchase. ASYNC_NETWORK_IO can reflect slow result consumption as well as network conditions.

During an active stall, inspect sys.dm_os_waiting_tasks and current requests because aggregate completed-wait time may not yet include a long wait still in progress. Preserve blocking chains and request identities at that moment. A later top-waits report cannot reconstruct which transaction held a lock after it has disappeared.

Validate a change against a comparable workload interval. Record request volume, business operation mix, and user-facing latency alongside wait deltas. Lower waits on a quieter server are not evidence that tuning helped. The useful conclusion identifies a specific workload cause and shows that the same amount of useful work now completes with less delay.

Technical references: Microsoft Learn: Wait statistics · Microsoft Learn: Waiting tasks.