How to Analyze Wait Stats in SQL Server 2022

A look at wait stats can show where a server is struggling. SQL Server isn’t what I would call “good” at describing its problems, and it doesn’t fix them on its own, so the waits are one of the better places to start.

sys.dm_os_wait_stats returns a row for every wait type, and there are a lot of them. The wait types table in Microsoft’s documentation for the view had 953 entries as of September 2026. I don’t want to memorize them, so I wanted something that filtered out the noise for me. The query below groups the ones I care about into a handful of categories and drops the rest.

Before reading any of it, check how far back the numbers go. The same page says the statistics “aren’t persisted after the database engine restarts,” and that everything is cumulative since the last restart or the last time someone cleared them. sqlserver_start_time in sys.dm_os_sys_info gives you the restart time, so you know whether you’re looking at a day of history or a year of it.

If you’ve just inherited a server and need to understand its history, keep the existing wait statistics intact. Only clear the old data when you’re troubleshooting a live incident and need a fresh baseline. For those live investigations, run:

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

The Script

The script below puts each wait into one of five categories: CPU, Memory, Database Blocking, I/O or Tempdb Contention. It filters out the noise, then uses a single GROUPING SETS query to return both the detailed rows and a total per category in one pass.

-- Wait types by category.
WITH WaitCategories AS (
    SELECT
        wait_type,
        wait_time_ms,
        signal_wait_time_ms,
        waiting_tasks_count,
        CASE 
            -- Benign Wait Types
            WHEN wait_type IN (
                'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
                'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
                'CLR_SEMAPHORE', 'DBMIRROR_DBM_EVENT', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRROR_WORKER_QUEUE',
                'DBMIRRORING_CMD', 'DIRTY_PAGE_POLL', 'DISPATCHER_QUEUE_SEMAPHORE', 'EXECSYNC', 'FSAGENT',
                'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX', 'HADR_CLUSAPI_CALL',
                'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_LOGCAPTURE_WAIT', 'HADR_NOTIFICATION_DEQUEUE',
                'HADR_TIMER_TASK', 'HADR_WORK_QUEUE', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
                'MEMORY_ALLOCATION_EXT', 'ONDEMAND_TASK_QUEUE', 'PARALLEL_REDO_DRAIN_WORKER',
                'PARALLEL_REDO_LOG_CACHE', 'PARALLEL_REDO_TRAN_LIST', 'PARALLEL_REDO_WORKER_SYNC',
                'PARALLEL_REDO_WORKER_WAIT_WORK', 'PREEMPTIVE_HADR_LEASE_MECHANISM',
                'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_OS_LIBRARYOPS', 'PREEMPTIVE_OS_COMOPS',
                'PREEMPTIVE_OS_CRYPTOPS', 'PREEMPTIVE_OS_PIPEOPS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
                'PREEMPTIVE_OS_GENERICOPS', 'PREEMPTIVE_OS_VERIFYTRUST', 'PREEMPTIVE_OS_DELETESECURITYCONTEXT',
                'PREEMPTIVE_OS_REPORTEVENT', 'PREEMPTIVE_OS_FILEOPS', 'PREEMPTIVE_OS_DEVICEOPS',
                'PREEMPTIVE_OS_QUERYREGISTRY', 'PREEMPTIVE_OS_WRITEFILE', 'PREEMPTIVE_OS_WRITEFILEGATHER',
                'PREEMPTIVE_XE_CALLBACKEXECUTE', 'PREEMPTIVE_XE_DISPATCHER', 'PREEMPTIVE_XE_GETTARGETSTATE',
                'PREEMPTIVE_XE_SESSIONCOMMIT', 'PREEMPTIVE_XE_TARGETINIT', 'PREEMPTIVE_XE_TARGETFINALIZE',
                'POPULATE_LOCK_ORDINALS', 'PWAIT_ALL_COMPONENTS_INITIALIZED',
                'PWAIT_DIRECTLOGCONSUMER_GETNEXT', 'PWAIT_EXTENSIBILITY_CLEANUP_TASK',
                'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'QDS_ASYNC_QUEUE',
                'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'REQUEST_FOR_DEADLOCK_SEARCH',
                'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SLEEP_BPOOL_FLUSH', 'SLEEP_DBSTARTUP',
                'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
                'SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK', 'SLEEP_TASK', 'SLEEP_TEMPDBSTARTUP',
                'SNI_HTTP_ACCEPT', 'SOS_WORK_DISPATCHER', 'SP_SERVER_DIAGNOSTICS_SLEEP',
                'SOS_WORKER_MIGRATION', 'VDI_CLIENT_OTHER', 'SQLTRACE_BUFFER_FLUSH',
                'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'SQLTRACE_WAIT_ENTRIES', 'STARTUP_DEPENDENCY_MANAGER',
                'TRACEWRITE', 'WAIT_FOR_RESULTS', 'WAITFOR', 'WAITFOR_TASKSHUTDOWN', 'WAIT_XTP_HOST_WAIT',
                'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', 'WAIT_XTP_CKPT_CLOSE', 'WAIT_XTP_RECOVERY',
                'XE_BUFFERMGR_ALLPROCESSED_EVENT', 'XE_DISPATCHER_JOIN', 'XE_DISPATCHER_WAIT',
                'XE_LIVE_TARGET_TVF', 'XE_TIMER_EVENT')
                THEN 'Benign'
            -- CPU Wait Types
            WHEN wait_type IN (
                'CXPACKET', 'CXCONSUMER', 'SOS_SCHEDULER_YIELD', 'THREADPOOL')
                THEN 'CPU'
            -- Memory Wait Types
            WHEN wait_type IN (
                'MEMORY_GRANT_UPDATE', 'RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE')
                THEN 'Memory'
            -- Database Blocking Wait Types
            WHEN wait_type LIKE 'LCK_M_%' THEN 'Database Blocking'
            -- I/O Wait Types
            WHEN wait_type IN (
                'WRITELOG', 'BACKUPIO', 'ASYNC_IO_COMPLETION', 'PAGEIOLATCH_SH',
                'PAGEIOLATCH_UP', 'PAGEIOLATCH_EX', 'IO_COMPLETION', 'WRITE_COMPLETION', 'LOGBUFFER',
                'LOGMGR', 'LOGMGR_RESERVE_APPEND', 'DISKIO_SUSPEND')
                THEN 'I/O'
            -- Tempdb Contention Wait Types
            WHEN wait_type IN (
                'PAGELATCH_EX', 'LATCH_EX', 'PAGELATCH_SH', 'LATCH_SH', 'PAGELATCH_UP')
                THEN 'Tempdb Contention'
            -- Other Wait Types
            ELSE 'Other'
        END AS WaitCategory
    FROM sys.dm_os_wait_stats
)
-- Combine detailed and aggregated results using GROUPING SETS
SELECT
    CASE
        WHEN GROUPING(wait_type) = 0 THEN 'Detailed'
        ELSE 'Aggregated'
    END AS ResultType,
    WaitCategory,
    wait_type,
    SUM(wait_time_ms) AS wait_time_ms,
    SUM(signal_wait_time_ms) AS signal_wait_time_ms,
    SUM(waiting_tasks_count) AS waiting_tasks_count
FROM WaitCategories
WHERE 1=1
    AND WaitCategory NOT IN ('Benign', 'Other') -- Exclude 'Benign' and 'Other' categories
    AND waiting_tasks_count > 0
GROUP BY
    GROUPING SETS (
        (WaitCategory, wait_type), -- Detailed results
        (WaitCategory)             -- Aggregated results
    )
ORDER BY
    CASE
        WHEN GROUPING(wait_type) = 1 THEN 1 -- Place aggregated results at the bottom
        ELSE 0
    END,
    wait_time_ms DESC;

How to Read the Results

Rows labeled Detailed show each wait type the query tracks, and rows labeled Aggregated total each category. The detailed rows are for drilling in, and the aggregates at the bottom give a high-level view of where SQL Server is spending most of its time.

Don’t fixate on the single top wait type. A category made up of several smaller waits can add up to more than the one wait at the top of the list, so compare the category totals and not just the peaks.

The filter at the end of the query drops two categories. Benign is the list of waits that are safe to ignore, and I got that list from query 42 in Glenn Berry’s DMVs. Other is everything that isn’t in one of my categories, which means a wait type the query doesn’t know about won’t show up at all. If a server looks fine by this query and clearly isn’t, changing AND WaitCategory NOT IN ('Benign', 'Other') to AND WaitCategory <> 'Benign' shows what’s been hiding in Other.

The categories are my own, and the Tempdb Contention label is the loosest of them. PAGELATCH_EX waits happen in tempdb, but they also happen in user databases when many sessions insert rows at the end of an index on a sequential column, like an identity. Microsoft’s article on resolving PAGELATCH_EX contention covers that case, which it calls last-page insert contention. It also gives a way to tell the two apart. The wait_resource for a waiting session starts with the database ID, and an ID of 5 or higher is a user database, while 2 is tempdb. So if this query points at Tempdb Contention, check that before adding tempdb files.