Waits Since Restart: A Wait Stats Query With Uptime Rates

Waits since restart are the first thing to read when a SQL Server feels slow. The view sys.dm_os_wait_stats counts how long tasks waited, by reason, since the instance started. One query can rank the reasons, remove the idle ones, and add a rate based on the uptime.

Why Waits Since Restart Mislead

A wait type is a reason that a task could not continue. Examples are a disk read, a lock or a full log buffer. SQL Server adds up the time for each reason. Two problems spoil a raw list.

Background threads wait all day by design, and their waits top the list on an idle server. The totals also depend on uptime. A server that ran for a year shows bigger numbers than one that ran for a day.

The query keeps a list of background patterns in a VALUES clause. You can add a pattern without touching the rest. It then ranks the real waits and prints six measures for each.

DECLARE @uptimeHours decimal(12,2) = (SELECT DATEDIFF(SECOND, sqlserver_start_time, SYSDATETIME()) / 3600.0 FROM sys.dm_os_sys_info);
WITH Background (Pattern) AS (
    SELECT p FROM (VALUES (N'SLEEP[_]%'), (N'XE[_]%'), (N'BROKER[_]%'), (N'SQLTRACE[_]%'), (N'QDS[_]%'), (N'FT[_]%'), (N'CLR[_]%'),
        (N'PWAIT[_]%'), (N'PARALLEL[_]REDO[_]%'), (N'WAIT[_]XTP[_]%'),
        (N'HADR[_]CLUSAPI[_]CALL'), (N'HADR[_]FILESTREAM[_]IOMGR[_]IOCOMPLETION'), (N'HADR[_]LOGCAPTURE[_]WAIT'), (N'HADR[_]NOTIFICATION[_]DEQUEUE'),
        (N'HADR[_]TIMER[_]TASK'), (N'HADR[_]WORK[_]QUEUE'), (N'DBMIRROR[_]DBM[_]EVENT'), (N'DBMIRROR[_]DBM[_]MUTEX'), (N'DBMIRROR[_]EVENTS[_]QUEUE'),
        (N'DBMIRROR[_]WORKER[_]QUEUE'),
        (N'LAZYWRITER[_]SLEEP'), (N'CHECKPOINT[_]QUEUE'), (N'LOGMGR[_]QUEUE'), (N'DIRTY[_]PAGE[_]POLL'), (N'DISPATCHER[_]QUEUE[_]SEMAPHORE'),
        (N'REQUEST[_]FOR[_]DEADLOCK[_]SEARCH'), (N'SERVER[_]IDLE[_]CHECK'), (N'SP[_]SERVER[_]DIAGNOSTICS[_]SLEEP'), (N'ONDEMAND[_]TASK[_]QUEUE'),
        (N'WAITFOR'), (N'WAITFOR[_]TASKSHUTDOWN'), (N'SOS[_]WORK[_]DISPATCHER'), (N'MEMORY[_]ALLOCATION[_]EXT'), (N'EXECSYNC'),
        (N'REDO[_]THREAD[_]PENDING[_]WORK'), (N'STARTUP[_]DEPENDENCY[_]MANAGER'), (N'UCS[_]%'), (N'CXCONSUMER'), (N'PVS[_]PREALLOCATE'),
        (N'PRINT[_]ROLLBACK[_]PROGRESS')) AS v(p)
)
SELECT TOP (10)
       w.wait_type,
       CAST(w.wait_time_ms / 1000.0 AS decimal(14,1)) AS WaitSeconds,
       w.waiting_tasks_count AS Tasks,
       CAST(w.wait_time_ms * 1.0 / NULLIF(w.waiting_tasks_count, 0) AS decimal(12,2)) AS AvgWaitMs,
       CAST(100.0 * w.signal_wait_time_ms / NULLIF(w.wait_time_ms, 0) AS decimal(5,1)) AS SignalPct,
       CAST(100.0 * w.wait_time_ms / SUM(w.wait_time_ms) OVER () AS decimal(5,1)) AS PctOfWaits,
       CAST(w.wait_time_ms / 1000.0 / @uptimeHours AS decimal(14,1)) AS SecondsPerHour
FROM sys.dm_os_wait_stats AS w
WHERE w.waiting_tasks_count > 0
  AND NOT EXISTS (SELECT 1 FROM Background AS b WHERE w.wait_type LIKE b.Pattern)
ORDER BY w.wait_time_ms DESC;

The table shows the ten rows of one run on a shared test server, so your list will differ. Read the columns in this order. PREEMPTIVE_OS_FILEOPS is a real wait, and it shows here because the list no longer hides every preemptive wait.

PctOfWaits shows how much of the real waiting each reason owns. The top reason is the first suspect. AvgWaitMs shows how long one wait lasts. LCK_M_X has few tasks and long waits, which points at a few blocked sessions. PAGELATCH_EX has millions of tasks and short waits, which points at a busy hot spot.

SignalPct is the share of the wait that a task spent queueing for a processor after its resource was ready. SOS_SCHEDULER_YIELD shows 99.6 percent, because that wait is nearly all queue time. SecondsPerHour divides the wait time by the uptime. It lets you compare two servers, or the same server on two days, even when the uptime differs.

Two practical notes come with the query. To hide another wait, add one row to the VALUES list. A pattern such as HADR[_]% would also hide HADR_SYNC_COMMIT, a real wait, so the list names the idle ones. Write an underscore inside square brackets, as in the list, so that the pattern matches a real underscore. The view needs the VIEW SERVER STATE permission. On SQL Server 2022 and later, that is VIEW SERVER PERFORMANCE STATE.

A second query shows the start time, the uptime that the rate used, and the number of processors.

SELECT sqlserver_start_time AS StartedAt,
       CAST(DATEDIFF(SECOND, sqlserver_start_time, SYSDATETIME()) / 3600.0 AS decimal(12,2)) AS UptimeHours,
       cpu_count AS Cpus
FROM sys.dm_os_sys_info;

What the Top Waits Point At

A wait name tells you where to look next. The table lists common waits, with the area they point at. It is a map. Each leader still needs its own investigation.

A parallel wait deserves a note. The counters add the wait time of every thread of a parallel query. A query with eight threads can collect eight seconds of waiting in one second.

A high share of parallel waits means that parallel queries run, which is normal on a reporting server. A reader’s list showed CXPACKET at 73 percent of the waits. That alone does not mean that 73 percent of the time is lost. The query leaves CXCONSUMER out, because that wait is the harmless half of the pair.

A Total Is Not a Trend

Waits since restart blend every hour of the uptime. A slow lunch hour disappears in a month of data. To see a window, take two readings and subtract them. The post Wait Stats Collection Script for Current SQL Server Versions stores snapshots on a schedule for that purpose.

A restart resets every counter. The first hours after a restart show a short history, and the percentages swing. Check the uptime before you trust the list. The idle list follows the collection post, but it names the HADR and mirroring waits one by one.

Is a Single Query Enough?

You could argue that a single query is too crude, and that a monitoring tool does the job better. A tool adds history and graphs, and it costs money and setup. The query costs nothing and runs in a moment. It works on any server you can connect to. I start with the query and add a tool when the problem needs a timeline.

What to Remember

Read the waits since restart with the idle ones removed. Rank by share, look at the average wait, and check the signal share for CPU. Divide by uptime when you compare two servers. Treat each leader as a pointer, and take a second reading when you need to know what changed.

A wait name is not a verdict, it is a place to look next.

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