Wait Stats Collection Script for Current SQL Server Versions

A wait stats collection script saves snapshots of sys.dm_os_wait_stats, so you can compare two of them. A single reading is a total since the last restart. It tells you what happened over weeks. A pair of snapshots tells you what happened between them, for example in the last ten minutes.

Why Wait Stats Collection Needs Snapshots

The view sys.dm_os_wait_stats counts every wait since the instance started, or since someone cleared the counters. A busy month buries today’s problem. If the server slowed down at 2 p.m., a total can’t say what changed at 2 p.m. Two snapshots can, because the difference between them covers only the time between them.

Three Small Tables

The demo database keeps three tables. WaitRun has one row for each snapshot, with its time and the start time of the instance. WaitRunDetail holds the waits of each run. IgnoredWait lists the idle waits as patterns, so you can add one without touching the script. Idle waits are background threads waiting for work. They grow all day on a healthy server and say nothing about your queries.

IF DB_ID(N'WaitCollectDemo') IS NULL CREATE DATABASE WaitCollectDemo;
GO
USE WaitCollectDemo;
GO
DROP PROCEDURE IF EXISTS dbo.TakeWaitSnapshot;
DROP TABLE IF EXISTS dbo.Work;
DROP TABLE IF EXISTS dbo.WaitRunDetail;
DROP TABLE IF EXISTS dbo.WaitRun;
DROP TABLE IF EXISTS dbo.IgnoredWait;
CREATE TABLE dbo.WaitRun (RunId int IDENTITY(1,1) NOT NULL PRIMARY KEY, TakenAt datetime2(0) NOT NULL, ServerStart datetime2(0) NOT NULL);
CREATE TABLE dbo.WaitRunDetail (
    RunId int NOT NULL REFERENCES dbo.WaitRun (RunId), WaitType nvarchar(60) NOT NULL,
    WaitingTasks bigint NOT NULL, WaitMs bigint NOT NULL, SignalMs bigint NOT NULL,
    CONSTRAINT PK_WaitRunDetail PRIMARY KEY (RunId, WaitType)
);
CREATE TABLE dbo.IgnoredWait (Pattern nvarchar(60) NOT NULL PRIMARY KEY);
INSERT INTO dbo.IgnoredWait (Pattern) VALUES
 (N'SLEEP[_]%'), (N'XE[_]%'), (N'BROKER[_]%'), (N'SQLTRACE%'), (N'FT[_]IFTS%'), (N'LAZYWRITER[_]SLEEP'), (N'CHECKPOINT[_]QUEUE'),
 (N'LOGMGR[_]QUEUE'), (N'REQUEST[_]FOR[_]DEADLOCK[_]SEARCH'), (N'SERVER[_]IDLE[_]CHECK'), (N'WAITFOR%'), (N'DISPATCHER[_]QUEUE[_]SEMAPHORE'),
 (N'SP[_]SERVER[_]DIAGNOSTICS[_]SLEEP'), (N'QDS[_]%'), (N'DIRTY[_]PAGE[_]POLL'), (N'ONDEMAND[_]TASK[_]QUEUE'), (N'CLR[_]%'), (N'PWAIT[_]%'),
 (N'HADR[_]FILESTREAM[_]IOMGR[_]IOCOMPLETION'), (N'MEMORY[_]ALLOCATION[_]EXT'), (N'SOS[_]WORK[_]DISPATCHER'), (N'PRINT[_]ROLLBACK[_]PROGRESS');

The Snapshot Procedure

The procedure copies the current counters into the tables. It stores only waits that have happened at least once, which keeps each snapshot small. It also stores sqlserver_start_time, because a restart resets every counter. That column lets the report notice when two snapshots sit on opposite sides of a restart.

CREATE PROCEDURE dbo.TakeWaitSnapshot
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @run int;
    INSERT INTO dbo.WaitRun (TakenAt, ServerStart) SELECT SYSDATETIME(), sqlserver_start_time FROM sys.dm_os_sys_info;
    SET @run = SCOPE_IDENTITY();
    INSERT INTO dbo.WaitRunDetail (RunId, WaitType, WaitingTasks, WaitMs, SignalMs)
    SELECT @run, wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
    FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0;
END;

In production, run the wait stats collection from a SQL Server Agent job every 10 or 15 minutes. Here you run it by hand, once before the load and once after. The first call also shows how many wait types one snapshot stores.

Create a Little Load

The load is 3,000 inserts, each in its own transaction. Every commit must wait for the log to reach disk, so the waits show up as WRITELOG.

SET NOCOUNT ON;
EXEC dbo.TakeWaitSnapshot;
SELECT COUNT(*) AS WaitTypesStored FROM dbo.WaitRunDetail;
CREATE TABLE dbo.Work (Id int IDENTITY(1,1) PRIMARY KEY, Filler char(200) NOT NULL DEFAULT 'x');
DECLARE @i int = 0;
WHILE @i < 3000 BEGIN INSERT INTO dbo.Work DEFAULT VALUES; SET @i += 1; END;
EXEC dbo.TakeWaitSnapshot;

The number depends on your instance and its history. The second snapshot is now in the tables, and the report can compare the two.

Report What Changed

The report takes the newest snapshot and the one before it. If the instance restarted in between, it ignores the older snapshot, since the counters started again from zero. The report then shows the totals since the restart. Read the first report after a restart as a total, not as an interval. Then it subtracts, drops the idle waits and gives each wait a category. The report covers the whole instance, so run it on a quiet one.

The categories are this script’s own grouping, not Microsoft’s. Disk covers log and data page waits. Locks, latches, parallelism, client, memory and CPU follow the wait name. Everything else is Other. The signal column is the time a task waited for a CPU after its resource was ready.

DECLARE @new int = (SELECT MAX(RunId) FROM dbo.WaitRun);
DECLARE @old int = (SELECT MAX(RunId) FROM dbo.WaitRun WHERE RunId < @new);
IF (SELECT ServerStart FROM dbo.WaitRun WHERE RunId = @old) <> (SELECT ServerStart FROM dbo.WaitRun WHERE RunId = @new) SET @old = NULL;
SELECT TOP (8) n.WaitType,
       CASE WHEN n.WaitType LIKE N'PAGEIOLATCH%' OR n.WaitType IN (N'WRITELOG', N'IO_COMPLETION', N'ASYNC_IO_COMPLETION', N'BACKUPIO') THEN N'Disk'
            WHEN n.WaitType LIKE N'LCK[_]M[_]%' THEN N'Locks'
            WHEN n.WaitType LIKE N'PAGELATCH%' OR n.WaitType LIKE N'LATCH[_]%' THEN N'Latches'
            WHEN n.WaitType LIKE N'CX%' THEN N'Parallelism'
            WHEN n.WaitType = N'ASYNC_NETWORK_IO' THEN N'Client'
            WHEN n.WaitType IN (N'RESOURCE_SEMAPHORE', N'CMEMTHREAD') THEN N'Memory'
            WHEN n.WaitType = N'SOS_SCHEDULER_YIELD' THEN N'CPU'
            ELSE N'Other' END AS Category,
       n.WaitingTasks - ISNULL(o.WaitingTasks, 0) AS Waits,
       n.WaitMs - ISNULL(o.WaitMs, 0) AS WaitMs,
       n.SignalMs - ISNULL(o.SignalMs, 0) AS SignalMs,
       CAST(100.0 * (n.WaitMs - ISNULL(o.WaitMs, 0)) / NULLIF(SUM(n.WaitMs - ISNULL(o.WaitMs, 0)) OVER (), 0) AS decimal(5,1)) AS PctOfDelta
FROM dbo.WaitRunDetail n
LEFT JOIN dbo.WaitRunDetail o ON o.RunId = @old AND o.WaitType = n.WaitType
WHERE n.RunId = @new AND n.WaitMs - ISNULL(o.WaitMs, 0) > 0
  AND NOT EXISTS (SELECT 1 FROM dbo.IgnoredWait i WHERE n.WaitType LIKE i.Pattern)
ORDER BY WaitMs DESC;

WRITELOG took almost all of the waiting time. It has 3,019 waits for the 3,000 commits, plus a few from other work on the instance. Of its 295 ms, 78 ms was signal time, so most of the delay was the log flush itself. The second row is a one-off wait from the instance. Its size and the extra rows vary from run to run. That is the shape of a result: one clear leader, then noise.

Where to Look Next

A Disk leader sends you to the storage and the transaction log. Locks send you to blocking sessions and long transactions. Latches point at hot pages in a few tables. A high signal share on many waits points at the processors. Client waits mean the application reads its results slowly. Each leader is a lead, and the next query depends on it.

Keep the History Small

Snapshots pile up. A job that runs every 15 minutes adds about 100 runs a day. Delete the old ones, details first, because of the foreign key. This script keeps 30 days.

DELETE FROM dbo.WaitRunDetail WHERE RunId IN (SELECT RunId FROM dbo.WaitRun WHERE TakenAt < DATEADD(DAY, -30, SYSDATETIME()));
DELETE FROM dbo.WaitRun WHERE TakenAt < DATEADD(DAY, -30, SYSDATETIME());

Two Notes on Versions and Resets

Since SQL Server 2016, sys.dm_exec_session_wait_stats shows the waits of one session. It answers a different question, namely what one query waited for. The instance wide view is still the right one for a collection job.

The command DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) resets the counters for the whole instance. On a shared server, that removes history other people use. Snapshots make it unnecessary, because the difference already starts from zero.

Is This Too Much for a Simple Question?

You could argue that a plain query over the totals is enough, especially on a server that restarted yesterday. For a quick look it is. A history is for the questions you can’t predict, such as what changed last Tuesday afternoon.

What to Remember

For wait stats collection, save snapshots, subtract them, and ignore the idle waits. Keep the instance start time with every snapshot. Read the result as a list of where time went. Treat each category as a place to look next, not as a diagnosis.

When you finish with the demo, drop the test database.

USE master;
GO
ALTER DATABASE WaitCollectDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE WaitCollectDemo;

A wait total is not a diagnosis, it is a history you have to slice.

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