Tuning Step One: Capture a Wait Stats Baseline Before Changing Anything

The server feels slow, and somebody already wants to change a setting. Capture a wait stats baseline first, so the next conversation includes evidence from the period that actually hurt.

Choose the Interval Before the Fix

The accumulated wait list tells you what happened across the instance's counter history. It does not isolate this morning's slowdown. A long-lived server blends yesterday's imports, overnight maintenance, and the current workload into the same totals.

I ask for the complaint window before asking for a tuning change. When did users notice the delay? What work was running then? Capture just before that work and again afterward. Use a representative busy interval when the complaint is continuous. An idle interval answers a different question.

Keep the start and end times with the readings. Also retain the engine startup time, since a restart breaks the counter sequence. Do not clear shared counters merely to make your report easier. Another investigation can be using the same history. Evidence does not improve when you erase it first.

Store Wait Stats Baseline Snapshots in Two Tables

Use a utility database on the instance being investigated. The following objects hold one snapshot header and its wait rows. Create them once, with an account authorized to create tables. Use distinct names if equivalent objects already exist.

Keep every wait type during capture. Apply exclusions when reporting. That lets you adjust the interpretation without recollecting the past. It also keeps counter-reset checks independent of whichever waits you decided to hide in the first report.

CREATE TABLE dbo.WaitSnapshot
(
    SnapshotID bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CapturedUtc datetime2(3) NOT NULL,
    EngineStartTime datetime2(3) NOT NULL
);
CREATE TABLE dbo.WaitSnapshotDetail
(
    SnapshotID bigint NOT NULL,
    WaitType nvarchar(60) NOT NULL,
    WaitingTasks bigint NOT NULL,
    WaitMs bigint NOT NULL,
    SignalMs bigint NOT NULL,
    CONSTRAINT PK_WaitSnapshotDetail PRIMARY KEY (SnapshotID, WaitType),
    CONSTRAINT FK_WaitSnapshotDetail_Header FOREIGN KEY (SnapshotID)
        REFERENCES dbo.WaitSnapshot (SnapshotID)
);

Run the Same Collector Twice

Run the next block before the chosen work. Repeat the entire block later in the same utility database. Each execution gets a new identity value. The transaction keeps a failed detail insert from leaving an apparently usable header behind.

These DMVs need appropriate server-state permissions. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for the wait view. Use an approved monitoring account rather than expanding an application's rights. The collector reads counters and writes only its own evidence tables.

A snapshot is a brief DMV read, not a perfectly simultaneous picture of every worker. Keep the collector small and record the intended interval. Very short windows magnify capture overhead and timing differences. A useful baseline should represent the workload you intend to improve.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.WaitSnapshot (CapturedUtc, EngineStartTime)
    SELECT SYSUTCDATETIME(), sqlserver_start_time
    FROM sys.dm_os_sys_info;
    DECLARE @SnapshotID bigint = CONVERT(bigint, SCOPE_IDENTITY());
    INSERT dbo.WaitSnapshotDetail
        (SnapshotID, WaitType, WaitingTasks, WaitMs, SignalMs)
    SELECT @SnapshotID, wait_type, waiting_tasks_count,
        wait_time_ms, signal_wait_time_ms
    FROM sys.dm_os_wait_stats;
    COMMIT TRANSACTION;
    SELECT SnapshotID, CapturedUtc, EngineStartTime
    FROM dbo.WaitSnapshot
    WHERE SnapshotID = @SnapshotID;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Reject a Broken Counter Sequence

Subtracting two snapshots assumes the counters belong to the same uninterrupted sequence. A different startup time proves they do not. A smaller ending counter also invalidates subtraction. Counter clearing can happen without a restart, so check both conditions.

The report uses the latest two completed snapshots for simplicity. In scheduled collection, select the exact IDs around the complaint window instead. Do not mix readings from different instances in these tables. Add a stable instance identifier if you build a central collector later.

A reset followed by enough new activity can exceed every old counter. A decrease test cannot detect that case. Coordinate counter clearing and record it separately. Treat a known reset as an invalid interval regardless of the arithmetic. The report rejects detected discontinuities rather than inventing negative waits.

Subtract Before Ranking Anything

The next block validates the pair and creates a temporary delta table. Run the later ranking query in the same session. A wait type appearing only in the ending snapshot uses zero as its starting value. Missing ending rows invalidate this sample comparison.

SignalMs is part of WaitMs, not additional time to add. Subtract it to obtain the non-signal component. WaitingTasks helps distinguish repeated short waits from fewer long waits. No single column identifies the guilty query or proves the underlying cause.

DECLARE @EndID bigint = (SELECT MAX(SnapshotID) FROM dbo.WaitSnapshot);
DECLARE @StartID bigint =
    (SELECT MAX(SnapshotID) FROM dbo.WaitSnapshot WHERE SnapshotID < @EndID);
IF @StartID IS NULL OR @EndID IS NULL
    THROW 50000, 'Capture two snapshots before comparing waits.', 1;
IF EXISTS
(
    SELECT 1
    FROM dbo.WaitSnapshot AS s
    JOIN dbo.WaitSnapshot AS e ON e.SnapshotID = @EndID
    WHERE s.SnapshotID = @StartID
      AND s.EngineStartTime <> e.EngineStartTime
)
    THROW 50001, 'The engine restarted between snapshots.', 1;
IF EXISTS
(
    SELECT 1
    FROM dbo.WaitSnapshotDetail AS s
    LEFT JOIN dbo.WaitSnapshotDetail AS e
        ON e.SnapshotID = @EndID AND e.WaitType = s.WaitType
    WHERE s.SnapshotID = @StartID
      AND (e.WaitType IS NULL OR e.WaitMs < s.WaitMs
        OR e.SignalMs < s.SignalMs OR e.WaitingTasks < s.WaitingTasks)
)
    THROW 50002, 'Wait counters decreased or disappeared. Use a new interval.', 1;
DROP TABLE IF EXISTS #WaitDelta;
SELECT e.WaitType,
    e.WaitMs - COALESCE(s.WaitMs, 0) AS WaitMs,
    e.SignalMs - COALESCE(s.SignalMs, 0) AS SignalMs,
    e.WaitingTasks - COALESCE(s.WaitingTasks, 0) AS WaitingTasks
INTO #WaitDelta
FROM dbo.WaitSnapshotDetail AS e
LEFT JOIN dbo.WaitSnapshotDetail AS s
    ON s.SnapshotID = @StartID AND s.WaitType = e.WaitType
WHERE e.SnapshotID = @EndID;
SELECT SnapshotID, CapturedUtc, EngineStartTime
FROM dbo.WaitSnapshot
WHERE SnapshotID IN (@StartID, @EndID)
ORDER BY SnapshotID;

Filter Background Noise Out of the Wait Stats Baseline

Idle workers and background tasks wait as part of normal operation. The following list removes several routine sleep and queue waits. It is a starting list, not a universal definition of harmlessness. Run the ranking once on a quiet server: whatever still leads there is background noise worth adding. Keep the raw data when a background task itself is under investigation.

Do not automatically discard every parallelism wait. Their interpretation depends on the plan and workload. Also examine waits intentionally introduced by application logic separately. A useful wait stats baseline reflects the complaint's context, including the work you deliberately asked the instance to do.

;WITH Relevant AS
(
    SELECT WaitType, WaitMs, SignalMs, WaitingTasks
    FROM #WaitDelta
    WHERE WaitMs > 0
      AND WaitType NOT IN
      (
          N'LAZYWRITER_SLEEP', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
          N'SLEEP_BPOOL_STEAL', N'SLEEP_BUFFERPOOL_HELPLW',
          N'CHECKPOINT_QUEUE', N'DIRTY_PAGE_POLL', N'REQUEST_FOR_DEADLOCK_SEARCH',
          N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
          N'BROKER_TASK_STOP', N'BROKER_EVENTHANDLER', N'BROKER_TO_FLUSH',
          N'LOGMGR_QUEUE', N'DISPATCHER_QUEUE_SEMAPHORE', N'SP_SERVER_DIAGNOSTICS_SLEEP',
          N'SOS_WORK_DISPATCHER', N'ONDEMAND_TASK_QUEUE', N'MEMORY_ALLOCATION_EXT',
          N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
      )
)
SELECT TOP (20) WaitType, WaitMs, SignalMs,
    WaitMs - SignalMs AS ResourceWaitMs, WaitingTasks,
    CAST(100.0 * CONVERT(decimal(28,2), WaitMs)
        / NULLIF(SUM(CONVERT(decimal(28,2), WaitMs)) OVER (), 0)
        AS decimal(9,2)) AS ShareOfFilteredWaits,
    CAST(1.0 * WaitMs / NULLIF(WaitingTasks, 0) AS decimal(18,2)) AS AverageWaitMs
FROM Relevant
ORDER BY WaitMs DESC, WaitType;

Read the Share as a Lead

ShareOfFilteredWaits uses only the included waits as its denominator. It describes their relative contribution, not the percentage of elapsed wall-clock time. Workers wait concurrently, so summed wait time can exceed the interval's duration. Keep that distinction beside the report.

These counters emphasize completed waits. A request still blocked at the ending snapshot also needs a current-wait inspection, using sys.dm_os_waiting_tasks and the active request views. Pair those live observations with the interval report. Otherwise, a long wait still in progress can be missing from your explanation.

A leading lock wait suggests investigating blockers and transaction boundaries. A leading page-I/O wait suggests examining the queries doing those reads and their access paths. A large signal component suggests checking runnable pressure and workload demand. These are directions for the next check, not instructions to buy hardware.

I compare the wait picture with throughput and user-visible latency. A faster workload can produce different wait proportions without being unhealthy. Conversely, a small wait total does not prove the server met its response-time goal. Look at completed work as well as accumulated waiting.

Keep Each Wait Stats Baseline Comparable

Save the workload description beside your selected snapshot IDs. Record maintenance, concurrency, and any configuration change. After one justified change, collect another representative pair. Compare the same business activity, not a busy morning with a quiet afternoon.

Retain enough snapshots to explain the tuning decision, then apply a sensible retention policy. Your wait stats baseline becomes useful evidence when the next person can reproduce its interval and filters. Make one change, measure again, and let that evidence decide the next step.

Related reading on this blog: Wait Stats Collection Scripts : Updated March 2021 and Detecting CPU Pressure with Wait Statistics.

A tuning baseline is not a guess with numbers, it is a record you can compare.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.