Wait statistics are the single best first step in any SQL Server performance investigation. Every time a query waits on a resource, whether that’s a lock, disk I/O, a transaction log flush, a memory grant, or something else, SQL Server records it. By the time a performance problem appears, the evidence is usually already sitting in sys.dm_os_wait_stats.
The challenge is that SQL Server tracks dozens of wait types, and most of them are noise, background workers sleeping, idle schedulers, Service Broker activity and other internal engine tasks.
This post walks through how to filter that out and focus on the waits that actually tell you something.
Why It Matters
Without wait statistics, a slow server is a black box. Is it the disk? Locking? CPU queue? Parallelism overhead? Memory pressure? Each of those requires a completely different fix, and the wrong diagnosis wastes hours.
Wait stats give you a ranked list of where time is being lost. If PAGEIOLATCH_SH is 60% of your total wait time, your I/O subsystem is the bottleneck. If WRITELOG dominates, your transaction log disk is the problem. If RESOURCE_SEMAPHORE appears, queries are queuing for memory grants. The stat points you at the right layer to investigate next.
The catch: SQL Server also records a lot of completely harmless waits, SLEEP_TASK, LAZYWRITER_SLEEP, BROKER_TO_FLUSH and others that represent SQL Server’s own background processes doing nothing. If you don’t filter these out, they dominate the output and hide the real signals.
The Script
WITH filtered_waits AS (
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
AND wait_type NOT IN (
'SLEEP_TASK', 'SLEEP_SYSTEMTASK',
'SLEEP_TEMPDBSTARTUP', 'SLEEP_DBSTARTUP',
'SLEEP_DCOMSTARTUP', 'SLEEP_MASTERDBREADY',
'SLEEP_MASTERMDREADY', 'SLEEP_MASTERUPGRADED',
'SLEEP_MSDBSTARTUP', 'SNI_HTTP_ACCEPT',
'DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'BROKER_EVENTHANDLER',
'BROKER_RECEIVE_WAITFOR', 'CHECKPOINT_QUEUE',
'DBMIRROR_EVENTS_QUEUE', 'DBMIRROR_WORKER_QUEUE',
'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SQLTRACE_BUFFER_FLUSH',
'SQLTRACE_WAIT_ENTRIES', 'WAITFOR',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE',
'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK',
'SP_SERVER_DIAGNOSTICS_SLEEP', 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT',
'HADR_WORK_QUEUE', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
'HADR_CLUSAPI_CALL', 'HADR_NOTIFICATION_DEQUEUE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'FT_IFTSHC_MUTEX',
'REPL_WORK_QUEUE', 'CLR_AUTO_EVENT',
'CLR_MANUAL_EVENT', 'WAIT_XTP_COMPILE_WAIT'
)
)
SELECT TOP 20
wait_type,
waiting_tasks_count,
wait_time_ms,
CAST(100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_total_wait,
CAST(wait_time_ms / NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,0)) AS avg_wait_ms,
max_wait_time_ms,
signal_wait_time_ms,
resource_wait_time_ms
FROM filtered_waits
ORDER BY wait_time_ms DESC;
The script removes the common idle and background waits so that the output focuses on waits that are more likely to indicate a real performance bottleneck.
How To Run It
# Table output - quick triage view
.\run.ps1 Get-WaitStatistics
# Save to CSV for comparison over time
.\run.ps1 Get-WaitStatistics -OutputFormat Csv
# Against a named instance
.\run.ps1 Get-WaitStatistics -ServerInstance MYSERVER\INST01 -OutputFormat Csv
Reading The Output
| Column | What It Means |
|---|---|
wait_type | Name of the wait. The SQL Server documentation has a full list. |
waiting_tasks_count | How many tasks have waited on this type since the last restart |
wait_time_ms | Total cumulative wait time in milliseconds since last restart |
pct_total_wait | This wait type’s share of all non-idle wait time. The most useful column for ranking. |
avg_wait_ms | Average wait time per occurrence, helps distinguish frequent short waits from rare long ones |
max_wait_time_ms | Longest single wait ever recorded for this type |
signal_wait_time_ms | Time spent waiting for a CPU scheduler slot after the resource became available. High values here indicate CPU pressure. |
resource_wait_time_ms | Time waiting for the actual resource (I/O, lock, memory, etc.) |
The pct_total_wait column is the one to sort by mentally. The top two or three wait types that together account for 60–80% of total wait time are your investigation targets.
Production Notes
PAGEIOLATCH_SH / PAGEIOLATCH_EX, Data page I/O. The query needed a page from disk and had to wait for it to load. Usually means either the buffer pool is too small (data doesn’t fit in RAM), or the storage subsystem is slow.
WRITELOG, Transaction log write waits. Every committed transaction must wait for its log records to be written. Dominates when the log disk is slow or when you have high-frequency small transactions.
RESOURCE_SEMAPHORE, Memory grant waits. Queries that sort or hash large datasets need a memory reservation. If available memory is low, queries queue here. Often associated with missing indexes causing large sort/hash operations.
CXPACKET / CXCONSUMER, Parallelism coordination. Some degree is normal. Very high values suggest MAXDOP is set too high, or cost threshold for parallelism is too low, causing even trivial queries to go parallel.
LCK_M_X / LCK_M_S, Lock waits. Exclusive and shared locks. High values mean blocking is happening regularly. Investigate with the blocking scripts.
ASYNC_NETWORK_IO, Client network waits. SQL Server produced results but the application wasn’t reading them fast enough. Usually an application-layer issue, not a SQL Server one.
signal_wait_time_ms is large relative to resource_wait_time_ms, This means queries got their resource but then waited for CPU. Points to CPU saturation / worker thread contention.
A healthy SQL Server usually has a mixture of waits rather than one dominant wait type. Small amounts of CXCONSUMER, CXPACKET and occasional PAGEIOLATCH_* waits are perfectly normal. When a single wait consistently accounts for 40% or more of total wait time, that’s usually where your investigation should begin.
Important: These Are Cumulative Since Last Restart
sys.dm_os_wait_stats accumulates from the moment SQL Server started. On a server that’s been up for 6 months, today’s problem might be buried under months of historical data. If you’re investigating a specific incident, compare two snapshots taken before and after the problem window, or use a baseline snapshot tool.
Related Scripts
Get-LongRunningQueries.sql, see which active queries are currently waiting and what typeGet-BlockingSessions.sql, ifLCK_M_*waits are high, start hereGet-DatabaseIoUsage.sql, ifPAGEIOLATCHis high, see which databases are driving I/O
Get The Scripts
The full script is available in the dba-tools repo on GitHub:
The Library, A to Z
Below you’ll find every documented SQL Server wait type in the library, listed alphabetically. Related wait types are grouped into family pages where they share the same underlying cause, while the major performance bottlenecks have dedicated pillar guides with complete, tool-backed troubleshooting workflows.
Every wait type below links to the most appropriate page, whether that’s an individual guide, a family page, or a pillar article.
| Wait Type | What It Tells You |
|---|---|
AM_INDBUILD_ALLOCATION | SQL Server AM_INDBUILD_ALLOCATION waits cover extent allocation during index builds; AM_SCHEMAMGR_UNSHARED_CACHE covers a column attribute cache. (family page) |
AM_SCHEMAMGR_UNSHARED_CACHE | SQL Server AM_INDBUILD_ALLOCATION waits cover extent allocation during index builds; AM_SCHEMAMGR_UNSHARED_CACHE covers a column attribute cache. (family page) |
ASSEMBLY_FILTER_HASHTABLE | SQL Server ASSEMBLY_LOAD, ASSEMBLY_FILTER_HASHTABLE, CLR_CRST and CLR_TASK_START waits cover CLR assembly loading and task startup. Usually benign. (family page) |
ASSEMBLY_LOAD | SQL Server ASSEMBLY_LOAD, ASSEMBLY_FILTER_HASHTABLE, CLR_CRST and CLR_TASK_START waits cover CLR assembly loading and task startup. Usually benign. (family page) |
ASYNC_DISKPOOL_LOCK | SQL Server ASYNC_DISKPOOL_LOCK waits are threads coordinating long file operations like creating, zeroing, or encrypting files. Watch it during TDE scans. |
ASYNC_IO_COMPLETION | SQL Server ASYNC_IO_COMPLETION waits indicate asynchronous file operations are taking time to complete, often backup, restore, or large maintenance I/O. |
ASYNC_NETWORK_IO | Client not consuming results fast enough (pillar) |
BACKUP | SQL Server BACKUP waits are backup threads synchronizing with each other during backup processing. Expected during backups; watch durations instead. |
BACKUPBUFFER | Backup buffer waits (pillar) |
BACKUPIO | Backup I/O throughput (pillar) |
BACKUPTHREAD | SQL Server BACKUPTHREAD waits are backup workers waiting for other threads during backup or restore. Long waits are normal; check backup duration. |
BAD_PAGE_PROCESS | SQL Server BAD_PAGE_PROCESS waits come from the suspect page logger. Any real presence means repeated 823/824 errors, corruption needing action now. |
BMPALLOCATION | SQL Server BMPALLOCATION waits relate to bitmap allocation in parallel query plans. Usually tiny; if visible, review parallelism and big hash joins. |
BPSORT | SQL Server BPSORT waits are threads coordinating batch-mode sorts. Watch for the 2016-era inefficiency where a batch sort behaves like a hung query. |
BROKER_CONNECTION_RECEIVE_TASK | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_ENDPOINT_STATE_MUTEX | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_EVENTHANDLER | SQL Server BROKER_EVENTHANDLER waits are the per-instance Service Broker event handler idling; its total roughly equals uptime. Benign, filter it. |
BROKER_INIT | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_MASTERSTART | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_RECEIVE_WAITFOR | SQL Server BROKER_RECEIVE_WAITFOR waits are sessions blocked on RECEIVE WAITFOR against an empty Service Broker queue. Expected; filter it out. |
BROKER_REGISTERALLENDPOINTS | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_SERVICE | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_SHUTDOWN | SQL Server BROKER_INIT, BROKER_MASTERSTART, BROKER_SERVICE, BROKER_SHUTDOWN and related startup and endpoint waits explained in one place. All benign. (family page) |
BROKER_TASK_STOP | SQL Server BROKER_TASK_STOP waits are Service Broker task handlers waiting up to ten seconds to shut down when no Broker work exists. Benign timer. |
BROKER_TO_FLUSH | SQL Server BROKER_TO_FLUSH waits are the Service Broker lazy flusher idling, ticking one second per second even when Broker is unused. Filter it out. |
BROKER_TRANSMISSION_TABLE | SQL Server BROKER_TRANSMISSION_TABLE waits cover writing inactive Service Broker transmission objects to a tempdb worktable. Benign housekeeping. |
BROKER_TRANSMISSION_WORK | SQL Server BROKER_TRANSMISSION_WORK waits relate to Service Broker transmission work availability, similar to BROKER_TRANSMITTER. Benign; filter it. |
BROKER_TRANSMITTER | SQL Server BROKER_TRANSMITTER waits are the two Service Broker transmitter threads idling on an empty transmission queue. Benign; grows 2s per second. |
CHECKPOINT_QUEUE | SQL Server CHECKPOINT_QUEUE waits are the background checkpoint process idling between requests. Completely benign, filter it out of wait analysis. |
CHECK_TABLES_INITIALIZATION | SQL Server CHECK_TABLES_INITIALIZATION waits are parallel DBCC CHECKDB threads taking turns through an initialization mutex. Benign DBCC overhead. |
CHECK_TABLES_THREAD_BARRIER | SQL Server CHECK_TABLES_THREAD_BARRIER waits are parallel DBCC threads waiting at sync barriers. Normal for parallel checks; beware the hang case. |
CHKPT | SQL Server CHKPT waits occur once at instance startup while the background checkpoint process waits for its start signal. Benign; expect exactly one. |
CLR_AUTO_EVENT | SQL Server CLR_AUTO_EVENT waits are CLR worker threads waiting on auto-reset events, normally just idle threads. Long waits are typical and expected. |
CLR_CRST | SQL Server ASSEMBLY_LOAD, ASSEMBLY_FILTER_HASHTABLE, CLR_CRST and CLR_TASK_START waits cover CLR assembly loading and task startup. Usually benign. (family page) |
CLR_MANUAL_EVENT | SQL Server CLR_MANUAL_EVENT waits are CLR worker threads waiting on manual-reset events, normally idle threads. Only unsafe assemblies change the story. |
CLR_SEMAPHORE | SQL Server CLR_SEMAPHORE waits are CLR tasks waiting on a semaphore. Usually benign; if it grows, review your CLR assemblies’ resource handling. |
CLR_TASK_START | SQL Server ASSEMBLY_LOAD, ASSEMBLY_FILTER_HASHTABLE, CLR_CRST and CLR_TASK_START waits cover CLR assembly loading and task startup. Usually benign. (family page) |
CMEMPARTITIONED | SQL Server CMEMPARTITIONED waits are contention on a partitioned memory object, the structure designed to relieve CMEMTHREAD pressure. Usually minor. |
CMEMTHREAD | SQL Server CMEMTHREAD waits show contention on shared memory objects under high concurrency, often hot metadata paths or compile pressure. |
COLUMNSTORE_BUILD_THROTTLE | SQL Server COLUMNSTORE_BUILD_THROTTLE waits are parallel columnstore build threads waiting while the first segment builds single-threaded to size grants. |
COMMIT_TABLE | SQL Server COMMIT_TABLE waits are contention on the hidden commit table behind Change Tracking. Many tracked tables plus small transactions drive it. |
CXCONSUMER | Parallelism consumer-side coordination (pillar) |
CXPACKET | Parallelism coordination, MAXDOP and cost threshold (pillar) |
CXROWSET_SYNC | SQL Server CXROWSET_SYNC waits happen when parallel scan threads coordinate access to the shared parent rowset. Benign; tune parallelism if unwanted. |
CXSYNC_CONSUMER | SQL Server CXSYNC_CONSUMER waits are consumer threads at parallel exchange sync points, split out of CXPACKET in SQL Server 2022 and Azure SQL. |
CXSYNC_PORT | SQL Server CXSYNC_PORT waits track opening, closing, and syncing exchange ports between parallel producer and consumer threads on SQL Server 2022+. |
DAC_INIT | SQL Server DAC_INIT waits occur while the Dedicated Admin Connection listener initializes at startup. One-time and benign; make sure DAC works though. |
DBMIRRORING_CMD | SQL Server DBMIRRORING_CMD waits cover mirroring configuration, state changes, and log flush waits. Usually benign; mirror log disk if it dominates. |
DBMIRROR_DBM_MUTEX | SQL Server DBMIRROR_DBM_MUTEX waits occur on a database mirror while parallel threads replay log records. Normally benign, seen only on the mirror. |
DBMIRROR_EVENTS_QUEUE | SQL Server DBMIRROR_EVENTS_QUEUE waits are the main database mirroring thread waiting for events. Benign whenever mirroring is configured; filter it. |
DBMIRROR_SEND | SQL Server DBMIRROR_SEND waits mean database mirroring cannot push log over the network fast enough, slowing the principal on synchronous sessions. |
DBMIRROR_WORKER_QUEUE | SQL Server DBMIRROR_WORKER_QUEUE waits are database mirroring worker tasks waiting for more work. Long waits on quiet databases are expected; benign. |
DEADLOCK_ENUM_MUTEX | SQL Server DEADLOCK_ENUM_MUTEX waits synchronize the deadlock monitor with sys.dm_os_waiting_tasks so only one deadlock search runs at a time. Benign. |
DIRTY_PAGE_POLL | SQL Server DIRTY_PAGE_POLL waits are the indirect checkpoint background task sleeping between polls for dirty pages. Purely benign, filter it out. |
DIRTY_PAGE_TABLE_LOCK | SQL Server DIRTY_PAGE_TABLE_LOCK waits are redo and read threads contending on the dirty page list of an AG readable secondary under heavy load. |
DISKIO_SUSPEND | SQL Server DISKIO_SUSPEND waits mean database I/O is frozen for an external snapshot backup and sessions are stalled until the tool thaws it. |
DISPATCHER_QUEUE_SEMAPHORE | SQL Server DISPATCHER_QUEUE_SEMAPHORE waits are dispatcher pool threads waiting for work, used by backups and In-Memory OLTP. Benign; filter it out. |
DPT_ENTRY_LOCK | SQL Server DPT_ENTRY_LOCK waits are AG secondary redo and read threads contending on the same dirty page entries, reads and writes on the same tables. |
DROPTEMP | SQL Server DROPTEMP waits are exponential back-off retries after a failed temp table drop, usually following a deadlock. Check the error log for details. |
DUMP_LOG_COORDINATOR | SQL Server DUMP_LOG_COORDINATOR and its queue variant occur while fn_dump_dblog reads log records from backups. Normal during log forensics. (family page) |
DUMP_LOG_COORDINATOR_QUEUE | SQL Server DUMP_LOG_COORDINATOR and its queue variant occur while fn_dump_dblog reads log records from backups. Normal during log forensics. (family page) |
EC | SQL Server EC waits mean pages are being read from a Buffer Pool Extension file. High values show BPE working hard; single-threaded scans feel it most. |
EE_PMOLOCK | SQL Server EE_PMOLOCK waits are threads synchronizing on a memory object used during statement execution. Rarely a contention point; check churn. |
EXCHANGE | SQL Server EXCHANGE waits occur inside the parallelism exchange iterator during parallel queries. Read it like CXPACKET, check MAXDOP and plans. |
EXECSYNC | SQL Server EXECSYNC waits occur when parallel threads wait for a single thread to build a shared construct like a bitmap or spool. Usually benign. |
FCB_REPLICA_READ | SQL Server FCB_REPLICA_READ and FCB_REPLICA_WRITE waits synchronize reads and writes of database snapshot sparse files, including DBCC snapshots. (family page) |
FCB_REPLICA_WRITE | SQL Server FCB_REPLICA_READ and FCB_REPLICA_WRITE waits synchronize reads and writes of database snapshot sparse files, including DBCC snapshots. (family page) |
FFT_NSO_DB_LIST | SQL Server FFT_NSO_DB_LIST and FFT_RECOVERY waits belong to the FileTable subsystem, covering its database list and startup recovery. Benign. (family page) |
FFT_RECOVERY | SQL Server FFT_NSO_DB_LIST and FFT_RECOVERY waits belong to the FileTable subsystem, covering its database list and startup recovery. Benign. (family page) |
FGCB_ADD_REMOVE | SQL Server FGCB_ADD_REMOVE waits mean sessions are queuing behind data file growth events. Pre-size files and enable instant file initialization. |
FT_IFTSHC_MUTEX | SQL Server FT_IFTSHC_MUTEX and FT_IFTS_RWLOCK waits are full-text search worker synchronization, including fdhost control operations. Benign; filter. (family page) |
FT_IFTS_RWLOCK | SQL Server FT_IFTSHC_MUTEX and FT_IFTS_RWLOCK waits are full-text search worker synchronization, including fdhost control operations. Benign; filter. (family page) |
FT_IFTS_SCHEDULER_IDLE_WAIT | SQL Server FT_IFTS_SCHEDULER_IDLE_WAIT waits are the full-text search scheduler idling with no work queued. Safely ignored; filter it out. |
FT_MASTER_MERGE | SQL Server FT_MASTER_MERGE waits are threads in a full-text master merge waiting on each other. Stagger indexing and reorganize jobs to avoid issues. |
HADR_AG_MUTEX | SQL Server HADR_AG_MUTEX waits are threads queuing for exclusive access to an Availability Group’s configuration during DDL and cluster commands. |
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST | SQL Server HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST and HADR_FILESTREAM_IOMGR waits are AG internal locks around events and FILESTREAM startup. (family page) |
HADR_CLUSAPI_CALL | SQL Server HADR_CLUSAPI_CALL waits are threads calling Windows Failover Cluster APIs for Availability Group operations. Normally benign background noise. |
HADR_DATABASE_FLOW_CONTROL | SQL Server HADR_DATABASE_FLOW_CONTROL waits mean an Availability Group is throttling log send because the network or secondary cannot keep up. |
HADR_FILESTREAM_IOMGR | SQL Server HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST and HADR_FILESTREAM_IOMGR waits are AG internal locks around events and FILESTREAM startup. (family page) |
HADR_FILESTREAM_IOMGR_IOCOMPLETION | SQL Server HADR_FILESTREAM_IOMGR_IOCOMPLETION waits are a FILESTREAM AG timer ticking every half second, even with no AGs configured. Filter it out. |
HADR_GROUP_COMMIT | SQL Server HADR_GROUP_COMMIT waits are Availability Group commits batching into shared log blocks. Expected optimization; tunable if throughput drops. |
HADR_LOGCAPTURE_WAIT | SQL Server HADR_LOGCAPTURE_WAIT means an Availability Group log capture thread is waiting for new log to ship. Expected and benign, filter it out. |
HADR_LOGPROGRESS_SYNC | SQL Server HADR_LOGPROGRESS_SYNC waits guard the log-harden LSN structure that releases synchronous AG commits. If it grows, HADR_SYNC_COMMIT grows too. |
HADR_NOTIFICATION_DEQUEUE | SQL Server HADR_NOTIFICATION_DEQUEUE waits are a background task waiting for the next Windows cluster notification. Expected and benign on AG servers. |
HADR_SEEDING_LIMIT_BACKUPS | SQL Server HADR_SEEDING_LIMIT_BACKUPS waits appear during Availability Group automatic seeding, often with very long durations. Benign; watch the DMVs. |
HADR_SYNC_COMMIT | AG synchronous commit latency (pillar) |
HADR_TIMER_TASK | SQL Server HADR_TIMER_TASK waits cover Availability Group timer scheduling, including the waits between periodic AG tasks. Benign, filter it out. |
HADR_WORK_POOL | SQL Server HADR_WORK_POOL waits are threads synchronizing on the Availability Group background work task object. Benign AG plumbing; filter it out. |
HADR_WORK_QUEUE | SQL Server HADR_WORK_QUEUE waits are Availability Group background workers waiting for work to be assigned. Completely benign, filter them out. |
HP_SPOOL_BARRIER | SQL Server HP_SPOOL_BARRIER waits came from a flawed parallel spool bug fix, added and removed across versions. If you see it, patch to current CUs. |
HTBUILD | SQL Server HTBUILD waits are threads synchronizing while building a shared batch-mode hash table. High values usually mean data skew across threads. |
HTDELETE | SQL Server HTDELETE waits are threads synchronizing at the end of a batch-mode hash join. Common with columnstore; high values usually mean thread skew. |
HTMEMO | SQL Server HTMEMO waits are batch-mode threads synchronizing before scanning the shared hash table to output matches. Driven by thread skew. |
HTREINIT | SQL Server HTREINIT waits are batch-mode threads synchronizing before resetting a hash join for the next partial join. Part of the HT* skew family. |
HTREPARTITION | SQL Server HTREPARTITION waits are batch-mode threads synchronizing while repartitioning a shared hash table, usually after it outgrew its memory. |
IMPPROV_IOWAIT | SQL Server IMPPROV_IOWAIT waits are bulk load operations waiting on reads from the source file. Slow source storage or network shares are the usual cause. |
IO_COMPLETION | Non-data-page I/O: spills, backups, DBCC (pillar) |
IO_QUEUE_LIMIT | SQL Server IO_QUEUE_LIMIT waits mean a session’s asynchronous I/O queue is full and new I/O is throttled, mainly seen in Azure SQL Database. |
KSOURCE_WAKEUP | SQL Server KSOURCE_WAKEUP waits are the service control task waiting for Service Control Manager requests like shutdown. One eternal wait; benign. |
LATCH_DT | SQL Server LATCH_DT waits are for a destroy-mode latch on a non-page structure. Extremely rare; find the latch class in sys.dm_os_latch_stats. |
LATCH_EX | SQL Server LATCH_EX waits are contention on a non-page latch. The latch class in sys.dm_os_latch_stats tells you which subsystem is hot. |
LATCH_KP | SQL Server LATCH_KP waits are keep-mode latches on non-page structures, pinning them without blocking readers. Near zero always; check latch classes. |
LATCH_NL | SQL Server LATCH_NL waits are the null latch mode, present for completeness and essentially never used. Always zero; nothing to investigate. |
LATCH_SH | SQL Server LATCH_SH waits are shared-latch contention on an internal non-page structure. The latch class in sys.dm_os_latch_stats identifies the subsystem. |
LATCH_UP | SQL Server LATCH_UP waits are update-mode latches on internal non-page structures. Diagnose by latch class in sys.dm_os_latch_stats, not by mode. |
LAZYWRITER_SLEEP | SQL Server LAZYWRITER_SLEEP waits are the lazy writer sleeping between buffer pool checks, one second at a time. Benign; filter it out of analysis. |
LCK_M_BU_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_BU_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IS | SQL Server LCK_M_IS waits mean a reader cannot get an Intent Shared lock, usually because an exclusive table lock or lock escalation is blocking it. |
LCK_M_IS_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IS_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IU_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IU_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IX | SQL Server LCK_M_IX waits mean a writer cannot get an Intent Exclusive lock on a table or page, usually blocked by shared table locks or escalation. |
LCK_M_IX_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_IX_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_NL | SQL Server LCK_M_RIn_NL waits are blocked insert-range key locks under SERIALIZABLE isolation, typically from concurrent inserts probing the same range. |
LCK_M_RIN_NL_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_NL_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_S_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_S_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_U_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_U_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_X_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RIN_X_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RS_S | SQL Server LCK_M_RS_S waits are blocked shared range locks from SERIALIZABLE isolation, often introduced silently by distributed transactions or ORMs. |
LCK_M_RS_S_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RS_S_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RS_U | SQL Server LCK_M_RS_U waits are blocked update-range key locks under SERIALIZABLE isolation, typical of update-where-key patterns colliding on a range. |
LCK_M_RS_U_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RS_U_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_S_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_S_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_U_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_U_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_X | SQL Server LCK_M_RX_X waits are blocked exclusive range locks under SERIALIZABLE isolation, a common deadlock ingredient in insert-heavy workloads. |
LCK_M_RX_X_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_RX_X_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_S | Shared lock blocking (pillar) |
LCK_M_SCH_M | SQL Server LCK_M_SCH_M waits mean DDL is stuck waiting for a Schema Modification lock, usually behind long-running queries or open transactions. |
LCK_M_SCH_M_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SCH_M_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SCH_S | SQL Server LCK_M_SCH_S waits mean queries cannot even compile because DDL holds a schema modification lock. Usually an index rebuild or ALTER TABLE. |
LCK_M_SCH_S_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SCH_S_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SIU_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SIU_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SIX | SQL Server LCK_M_SIX waits mean a session needs a Shared With Intent Exclusive lock, a scan-then-update pattern blocked by other table-level locks. |
LCK_M_SIX_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_SIX_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_S_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_S_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_U | Update lock blocking (pillar) |
LCK_M_UIX_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_UIX_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_U_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_U_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_X | Exclusive lock blocking (pillar) |
LCK_M_X_ABORT_BLOCKERS | WAIT_AT_LOW_PRIORITY online index option (family page) |
LCK_M_X_LOW_PRIORITY | WAIT_AT_LOW_PRIORITY online index option (family page) |
LOGBUFFER | SQL Server LOGBUFFER waits point to pressure in log buffer handling before flush, often from very high commit rates or bursty transaction patterns. |
LOGMGR | SQL Server LOGMGR waits occur while a database waits for outstanding log I/O to finish before closing. Rare, and usually tied to slow log storage. |
LOGMGR_FLUSH | SQL Server LOGMGR_FLUSH waits mean a thread generating log records is waiting for the current log flush to finish. Treat it like WRITELOG pressure. |
LOGMGR_QUEUE | SQL Server LOGMGR_QUEUE waits are the log writer threads waiting for work. One of the most common benign waits on every instance; filter it out. |
LOGMGR_RESERVE_APPEND | SQL Server LOGMGR_RESERVE_APPEND waits mean the transaction log is full and threads wait for truncation to free space. Size the log properly. |
LOGPOOL_CACHESIZE | SQL Server LOGPOOL_CACHESIZE, CONSUMER, CONSUMERSET, FREEPOOLS and REPLACEMENTSET waits cover the log pool cache used by AG and CDC log readers. (family page) |
LOGPOOL_CONSUMER | SQL Server LOGPOOL_CACHESIZE, CONSUMER, CONSUMERSET, FREEPOOLS and REPLACEMENTSET waits cover the log pool cache used by AG and CDC log readers. (family page) |
LOGPOOL_CONSUMERSET | SQL Server LOGPOOL_CACHESIZE, CONSUMER, CONSUMERSET, FREEPOOLS and REPLACEMENTSET waits cover the log pool cache used by AG and CDC log readers. (family page) |
LOGPOOL_FREEPOOLS | SQL Server LOGPOOL_CACHESIZE, CONSUMER, CONSUMERSET, FREEPOOLS and REPLACEMENTSET waits cover the log pool cache used by AG and CDC log readers. (family page) |
LOGPOOL_REPLACEMENTSET | SQL Server LOGPOOL_CACHESIZE, CONSUMER, CONSUMERSET, FREEPOOLS and REPLACEMENTSET waits cover the log pool cache used by AG and CDC log readers. (family page) |
MEMORY_ALLOCATION_EXT | SQL Server MEMORY_ALLOCATION_EXT waits are threads switching to preemptive mode to allocate memory. Usually benign noise on every busy instance. |
METADATA_LAZYCACHE_RWLOCK | SQL Server METADATA_LAZYCACHE_RWLOCK waits guard lazily-populated metadata caches; replaced by MD_LAZYCACHE_RWLOCK from SQL Server 2012 onward. |
MSQL_DQ | SQL Server MSQL_DQ waits mean a task is waiting for a distributed query against a linked server to finish. Tune the remote side and data movement. |
MSQL_XP | SQL Server MSQL_XP waits measure time inside extended stored procedures. The wait is as long as the XP runs; find and troubleshoot the XP itself. |
OLEDB | SQL Server OLEDB waits mean a worker is blocked on an OLE DB provider call, often linked servers or DBCC CHECKDB, and the delay is external. |
ONDEMAND_TASK_QUEUE | SQL Server ONDEMAND_TASK_QUEUE waits are a background task waiting for high-priority system requests like DTC commits. Long waits are good news. |
PAGEIOLATCH_DT | SQL Server PAGEIOLATCH_DT waits are destroy-mode buffer latches during I/O. Essentially never significant; long waits indicate disk subsystem issues. |
PAGEIOLATCH_EX | Data page write I/O (pillar) |
PAGEIOLATCH_KP | SQL Server PAGEIOLATCH_KP waits are keep-mode latches on pages in I/O requests. Mode completeness only; SH and EX carry the real I/O latch signal. |
PAGEIOLATCH_NL | SQL Server PAGEIOLATCH_NL is the null mode of the I/O page latch family, unused in practice. Always zero; check SH and EX for real I/O latch issues. |
PAGEIOLATCH_SH | Data page read I/O, buffer pool pressure or slow storage (pillar) |
PAGEIOLATCH_UP | SQL Server PAGEIOLATCH_UP waits mean a thread is reading an allocation bitmap page from disk before updating it. Long waits point at slow storage. |
PAGELATCH_DT | SQL Server PAGELATCH_DT waits are destroy-mode latches on in-memory pages, present for mode completeness and essentially never a real signal. |
PAGELATCH_EX | In-memory page latch, tempdb allocation contention (pillar) |
PAGELATCH_KP | SQL Server PAGELATCH_KP waits are keep-mode latches pinning in-memory pages against destruction. Compatible with everything but destroy; near zero. |
PAGELATCH_NL | SQL Server PAGELATCH_NL is the null page latch mode, present for completeness and not used in practice. Always zero; the real modes carry the signal. |
PAGELATCH_SH | SQL Server PAGELATCH_SH waits mean threads are queuing for a shared latch on an in-memory page, usually tempdb allocation pages or an application hot page. |
PAGELATCH_UP | SQL Server PAGELATCH_UP waits are update-mode latches on in-memory pages, classically PFS and SGAM contention in tempdb. Check the page numbers. |
PARALLEL_BACKUP_QUEUE | SQL Server PARALLEL_BACKUP_QUEUE waits occur while parallel restore threads serialize output for RESTORE HEADERONLY, FILELISTONLY, or LABELONLY. |
PARALLEL_REDO_DRAIN_WORKER | SQL Server PARALLEL_REDO_DRAIN_WORKER waits are the main redo thread draining outstanding work at sync points like checkpoints. Benign redo behavior. |
PARALLEL_REDO_FLOW_CONTROL | SQL Server PARALLEL_REDO_FLOW_CONTROL waits mean the main AG redo thread is waiting for parallel redo workers to catch up on a secondary replica. |
PARALLEL_REDO_LOG_CACHE | SQL Server PARALLEL_REDO_LOG_CACHE waits occur occasionally after redo flow-control bottlenecks on AG replicas. Part of normal parallel redo; benign. |
PARALLEL_REDO_TRAN_LIST | SQL Server PARALLEL_REDO_TRAN_LIST waits are the main redo thread accessing the list of transactions being redone on an AG replica. Benign bookkeeping. |
PARALLEL_REDO_TRAN_TURN | SQL Server PARALLEL_REDO_TRAN_TURN waits are redo threads forced to apply log records in order. Page splits on the primary are the classic driver. |
PARALLEL_REDO_WORKER_SYNC | SQL Server PARALLEL_REDO_WORKER_SYNC waits are the main AG redo thread waiting for its worker threads to complete. Normal redo behavior; filter it out. |
PARALLEL_REDO_WORKER_WAIT_WORK | SQL Server PARALLEL_REDO_WORKER_WAIT_WORK waits are AG parallel redo workers idling with nothing to replay. Expected on secondaries, filter it out. |
PERFORMANCE_COUNTERS_RWLOCK | SQL Server PERFORMANCE_COUNTERS_RWLOCK waits synchronize adding and removing performance counter instances, like per-database counters. Benign. |
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL | SQL Server PREEMPTIVE_OS_CLUSTEROPS and PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL waits track Windows cluster API calls from FCIs and AGs. Benign. (family page) |
PREEMPTIVE_COM_COCREATEINSTANCE | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_CREATEACCESSOR | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_GETDATA | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_QUERYINTERFACE | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_RELEASEACCESSOR | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_RELEASEROWS | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_COM_SEQSTRMREAD | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_CREATEPARAM | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_DTC_ABORTREQUESTDONE | SQL Server PREEMPTIVE_DTC_BEGINTRANSACTION, ENLIST, ABORTREQUESTDONE and OS_DTCOPS waits track MSDTC calls. Slow DTC means slow distributed commits. (family page) |
PREEMPTIVE_DTC_BEGINTRANSACTION | SQL Server PREEMPTIVE_DTC_BEGINTRANSACTION, ENLIST, ABORTREQUESTDONE and OS_DTCOPS waits track MSDTC calls. Slow DTC means slow distributed commits. (family page) |
PREEMPTIVE_DTC_ENLIST | SQL Server PREEMPTIVE_DTC_BEGINTRANSACTION, ENLIST, ABORTREQUESTDONE and OS_DTCOPS waits track MSDTC calls. Slow DTC means slow distributed commits. (family page) |
PREEMPTIVE_FILESIZEGET | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_FSRECOVER_UNCONDITIONALUNDO | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_HADR_LEASE_MECHANISM | SQL Server PREEMPTIVE_HADR_LEASE_MECHANISM waits track AG lease renewal after a lease timeout. Presence means AG health trouble; check the network. |
PREEMPTIVE_OLEDBOPS | SQL Server PREEMPTIVE_OLEDBOPS waits are threads in preemptive mode talking to OLE DB providers, the linked server plumbing beside OLEDB and MSQL_DQ. |
PREEMPTIVE_OLEDB_SETPROPERTIES | SQL Server PREEMPTIVE_COM_GETDATA, QUERYINTERFACE, and related COM waits track calls into COM objects, mostly linked server providers. Tune the remote. (family page) |
PREEMPTIVE_OS_ACCEPTSECURITYCONTEXT | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_ACQUIRECREDENTIALSHANDLE | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_AUTHENTICATIONOPS | SQL Server PREEMPTIVE_OS_AUTHENTICATIONOPS waits track Windows authentication calls. Long waits usually mean slow domain controllers, not SQL. |
PREEMPTIVE_OS_AUTHORIZATIONOPS | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_AUTHZGETINFORMATIONFROMCONTEXT | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_AUTHZINITIALIZECONTEXTFROMSID | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_AUTHZINITIALIZERESOURCEMANAGER | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_BACKUPREAD | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_BCRYPTIMPORTKEY | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_CLOSEHANDLE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_CLUSTEROPS | SQL Server PREEMPTIVE_OS_CLUSTEROPS and PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROL waits track Windows cluster API calls from FCIs and AGs. Benign. (family page) |
PREEMPTIVE_OS_COMOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_COMPLETEAUTHTOKEN | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_COPYFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_CREATEDIRECTORY | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_CREATEFILE | SQL Server PREEMPTIVE_OS_CREATEFILE waits are Windows CreateFile calls, opening as well as creating files. FILESTREAM and slow file systems drive it. |
PREEMPTIVE_OS_CRYPTACQUIRECONTEXT | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_CRYPTIMPORTKEY | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_CRYPTOPS | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_DECRYPTMESSAGE | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_DELETEFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_DELETESECURITYCONTEXT | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_DEVICEIOCONTROL | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_DEVICEOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_DIRSVC_NETWORKOPS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_DISCONNECTNAMEDPIPE | SQL Server PREEMPTIVE_OS_GETADDRINFO, WINSOCKOPS, DISCONNECTNAMEDPIPE and MESSAGEQUEUEOPS waits track network API calls like DNS lookups and sockets. (family page) |
PREEMPTIVE_OS_DOMAINSERVICESOPS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_DSGETDCNAME | SQL Server PREEMPTIVE_OS_DSGETDCNAME waits are Windows DsGetDcName calls locating a domain controller. Slow DC discovery means slow authentication. |
PREEMPTIVE_OS_DTCOPS | SQL Server PREEMPTIVE_DTC_BEGINTRANSACTION, ENLIST, ABORTREQUESTDONE and OS_DTCOPS waits track MSDTC calls. Slow DTC means slow distributed commits. (family page) |
PREEMPTIVE_OS_ENCRYPTMESSAGE | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_FILEOPS | SQL Server PREEMPTIVE_OS_FILEOPS waits are generic Windows file system calls made outside SQL Server scheduling. Usually backup or file-level activity. |
PREEMPTIVE_OS_FINDFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_FLUSHFILEBUFFERS | SQL Server PREEMPTIVE_OS_FLUSHFILEBUFFERS waits are FlushFileBuffers calls forcing writes to durable media. The top wait on SQL Server on Linux by design. |
PREEMPTIVE_OS_FORMATMESSAGE | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_FREECREDENTIALSHANDLE | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_FREELIBRARY | SQL Server PREEMPTIVE_OS_LOADLIBRARY, FREELIBRARY and LIBRARYOPS waits track DLL load and unload calls, mostly for XPs and diagnostic components. (family page) |
PREEMPTIVE_OS_GENERICOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_GETADDRINFO | SQL Server PREEMPTIVE_OS_GETADDRINFO, WINSOCKOPS, DISCONNECTNAMEDPIPE and MESSAGEQUEUEOPS waits track network API calls like DNS lookups and sockets. (family page) |
PREEMPTIVE_OS_GETCOMPRESSEDFILESIZE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETDISKFREESPACE | SQL Server PREEMPTIVE_OS_GETDISKFREESPACE waits are Windows GetDiskFreeSpace calls checking volume space, from engine checks and monitoring queries. |
PREEMPTIVE_OS_GETFILEATTRIBUTES | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETFILESIZE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETFINALFILEPATHBYHANDLE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETLONGPATHNAME | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETPROCADDRESS | SQL Server PREEMPTIVE_OS_GETPROCADDRESS waits track resolving extended stored procedure addresses in DLLs, and can absorb whole XP runtimes via an old bug. |
PREEMPTIVE_OS_GETVOLUMENAMEFORVOLUMEMOUNTPOINT | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_GETVOLUMEPATHNAME | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_INITIALIZESECURITYCONTEXT | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_LIBRARYOPS | SQL Server PREEMPTIVE_OS_LOADLIBRARY, FREELIBRARY and LIBRARYOPS waits track DLL load and unload calls, mostly for XPs and diagnostic components. (family page) |
PREEMPTIVE_OS_LOADLIBRARY | SQL Server PREEMPTIVE_OS_LOADLIBRARY, FREELIBRARY and LIBRARYOPS waits track DLL load and unload calls, mostly for XPs and diagnostic components. (family page) |
PREEMPTIVE_OS_LOGONUSER | SQL Server PREEMPTIVE_OS_LOGONUSER waits are Windows LogonUser calls, common with proxies and linked servers using mapped credentials. Check AD health. |
PREEMPTIVE_OS_LOOKUPACCOUNTSID | SQL Server PREEMPTIVE_OS_LOOKUPACCOUNTSID waits are Windows SID-to-name lookups, often hitting domain controllers. SUSER_SNAME loops are the classic cause. |
PREEMPTIVE_OS_MESSAGEQUEUEOPS | SQL Server PREEMPTIVE_OS_GETADDRINFO, WINSOCKOPS, DISCONNECTNAMEDPIPE and MESSAGEQUEUEOPS waits track network API calls like DNS lookups and sockets. (family page) |
PREEMPTIVE_OS_MOVEFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_NCRYPTIMPORTKEY | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_NETGROUPGETUSERS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_NETLOCALGROUPGETMEMBERS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_NETUSERGETGROUPS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_NETUSERGETLOCALGROUPS | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_NETUSERMODALSGET | SQL Server PREEMPTIVE_OS_NETGROUPGETUSERS, NETUSERGETGROUPS and related waits track AD group membership lookups. xp_logininfo is the classic driver. (family page) |
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_OPENDIRECTORY | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_PDH_WMI_INIT | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_PIPEOPS | SQL Server PREEMPTIVE_OS_PIPEOPS waits track Windows pipe operations, almost always xp_cmdshell. The wait lasts as long as the external command. |
PREEMPTIVE_OS_PROCESSOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_QUERYREGISTRY | SQL Server PREEMPTIVE_OS_QUERYREGISTRY waits are Windows registry calls. Normally benign, but a known SQL Server 2022 bug (fixed in CU5) inflated it. |
PREEMPTIVE_OS_QUERYSECURITYCONTEXTTOKEN | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_REMOVEDIRECTORY | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_REPORTEVENT | SQL Server PREEMPTIVE_OS_REPORTEVENT waits are Windows ReportEvent calls writing to the event log. Growth means something is logging heavily. |
PREEMPTIVE_OS_REVERTTOSELF | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_RSFXDEVICEOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_SECURITYOPS | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_SERVICEOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_SETENDOFFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_SETFILEPOINTER | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_SETFILEVALIDDATA | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_SETNAMEDSECURITYINFO | SQL Server PREEMPTIVE_OS security waits like INITIALIZESECURITYCONTEXT and AUTHORIZATIONOPS track Windows auth API calls. Slow AD makes them grow. (family page) |
PREEMPTIVE_OS_SQMLAUNCH | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_VERIFYSIGNATURE | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_VERIFYTRUST | SQL Server PREEMPTIVE_OS_CRYPTOPS, CRYPTIMPORTKEY, VERIFYTRUST and related waits track Windows crypto API calls for TDE, backups, and module signing. (family page) |
PREEMPTIVE_OS_VSSOPS | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_OS_WAITFORSINGLEOBJECT | SQL Server PREEMPTIVE_OS_WAITFORSINGLEOBJECT waits are threads synchronizing with external client processes. Troubleshoot alongside ASYNC_NETWORK_IO. |
PREEMPTIVE_OS_WINSOCKOPS | SQL Server PREEMPTIVE_OS_GETADDRINFO, WINSOCKOPS, DISCONNECTNAMEDPIPE and MESSAGEQUEUEOPS waits track network API calls like DNS lookups and sockets. (family page) |
PREEMPTIVE_OS_WRITEFILE | SQL Server PREEMPTIVE_OS file waits like COPYFILE, DELETEFILE, SETFILEVALIDDATA and GETFILESIZE track single Windows file API calls. One reference page. (family page) |
PREEMPTIVE_OS_WRITEFILEGATHER | SQL Server PREEMPTIVE_OS_WRITEFILEGATHER waits usually mean file growth is zero-filling new space. Enable instant file initialization and pre-size files. |
PREEMPTIVE_OS_WSASETLASTERROR | SQL Server PREEMPTIVE_OS_DEVICEOPS, VSSOPS, PROCESSOPS, SERVICEOPS, GENERICOPS and other one-API preemptive waits gathered in one reference page. (family page) |
PREEMPTIVE_SB_STOPENDPOINT | SQL Server PREEMPTIVE_SB_STOPENDPOINT waits are threads calling Windows while shutting down a Service Broker endpoint. Rare lifecycle marker. |
PREEMPTIVE_SP_SERVER_DIAGNOSTICS | SQL Server PREEMPTIVE_SP_SERVER_DIAGNOSTICS waits are the background thread running sp_server_diagnostics health checks. Benign; filter it out. |
PREEMPTIVE_XE_CALLBACKEXECUTE | SQL Server PREEMPTIVE_XE_SESSIONCOMMIT, TARGETINIT, TARGETFINALIZE, GETTARGETSTATE and CALLBACKEXECUTE waits cover XEvent session lifecycle calls. (family page) |
PREEMPTIVE_XE_GETTARGETSTATE | SQL Server PREEMPTIVE_XE_SESSIONCOMMIT, TARGETINIT, TARGETFINALIZE, GETTARGETSTATE and CALLBACKEXECUTE waits cover XEvent session lifecycle calls. (family page) |
PREEMPTIVE_XE_SESSIONCOMMIT | SQL Server PREEMPTIVE_XE_SESSIONCOMMIT, TARGETINIT, TARGETFINALIZE, GETTARGETSTATE and CALLBACKEXECUTE waits cover XEvent session lifecycle calls. (family page) |
PREEMPTIVE_XE_TARGETFINALIZE | SQL Server PREEMPTIVE_XE_SESSIONCOMMIT, TARGETINIT, TARGETFINALIZE, GETTARGETSTATE and CALLBACKEXECUTE waits cover XEvent session lifecycle calls. (family page) |
PREEMPTIVE_XE_TARGETINIT | SQL Server PREEMPTIVE_XE_SESSIONCOMMIT, TARGETINIT, TARGETFINALIZE, GETTARGETSTATE and CALLBACKEXECUTE waits cover XEvent session lifecycle calls. (family page) |
PRINT_ROLLBACK_PROGRESS | SQL Server PRINT_ROLLBACK_PROGRESS waits are an ALTER DATABASE with ROLLBACK IMMEDIATE waiting for killed transactions to finish rolling back. |
PVS_PREALLOCATE | SQL Server PVS_PREALLOCATE waits are the Accelerated Database Recovery background task pacing Persistent Version Store space preallocation. Benign. |
PWAIT_ALL_COMPONENTS_INITIALIZED | SQL Server PWAIT_ALL_COMPONENTS_INITIALIZED waits are background tasks waiting at startup for engine components to finish initializing. Benign. |
PWAIT_DIRECTLOGCONSUMER_GETNEXT | SQL Server PWAIT_DIRECTLOGCONSUMER_GETNEXT waits are log-reading threads waiting for the next log block to be supplied. Benign log reader idling. |
PWAIT_EXTENSIBILITY_CLEANUP_TASK | SQL Server PWAIT_EXTENSIBILITY_CLEANUP_TASK waits come from a Machine Learning Services background task sleeping 300 seconds at a time. Filter it out. |
PWAIT_HADR_WORKITEM_COMPLETED | SQL Server PWAIT_HADR_WORKITEM_COMPLETED waits track async Availability Group operations like adding or removing databases. Long waits mean a stall. |
QDS_ASYNC_QUEUE | SQL Server QDS_ASYNC_QUEUE waits are threads waiting on the queue of Query Store data being asynchronously persisted. Benign background machinery. |
QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP | SQL Server QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP waits are the Query Store cleanup task sleeping between runs. Benign timer; filter it out. |
QDS_DYN_VECTOR | SQL Server QDS_DYN_VECTOR waits are threads accessing a thread-safe Query Store data structure. Internal bookkeeping; not a contention point. |
QDS_LOADDB | SQL Server QDS_LOADDB waits are Query Store loading its data at database startup, which blocks queries until done. Trace flag 7752 makes it async. |
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP | SQL Server QDS_PERSIST_TASK_MAIN_LOOP_SLEEP waits are the Query Store background writer sleeping between flushes. Benign; filter it out. |
QDS_SHUTDOWN_QUEUE | SQL Server QDS_SHUTDOWN_QUEUE waits are a Query Store background task idling on its shutdown signal queue. Benign; common on Azure SQL, filter it out. |
QDS_STMT | SQL Server QDS_STMT waits are threads latching the Query Store hash map to register new queries. Ad hoc workloads should use AUTO capture mode. |
QRY_PROFILE_LIST_MUTEX | SQL Server QRY_PROFILE_LIST_MUTEX waits guard the query profiling statistics list. A known 2016/2017 hang with trace flag 7412 involved it (KB 4089239). |
QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN | SQL Server QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN waits are parallel offline index build threads synchronizing on shared sort files during the sort. |
QUERY_TASK_ENQUEUE_MUTEX | SQL Server QUERY_TASK_ENQUEUE_MUTEX waits appear when batch-mode query threads wait for sibling threads to finish initializing. Benign coordination. |
REDO_THREAD_PENDING_WORK | SQL Server REDO_THREAD_PENDING_WORK waits are an AG secondary’s redo thread waiting for more log to apply. Expected on quiet AGs, filter it out. |
REPLICA_WRITES | SQL Server REPLICA_WRITES waits are tasks waiting for page writes to database snapshots or DBCC internal replicas, tied to snapshot copy-on-write cost. |
REQUEST_DISPENSER_PAUSE | SQL Server REQUEST_DISPENSER_PAUSE waits occur while outstanding I/O drains so a snapshot backup can freeze the database. Partner wait to DISKIO_SUSPEND. |
REQUEST_FOR_DEADLOCK_SEARCH | SQL Server REQUEST_FOR_DEADLOCK_SEARCH waits are the deadlock monitor idling between searches, normally every 5 seconds. Benign; filter it out. |
RESERVED_MEMORY_ALLOCATION_EXT | SQL Server RESERVED_MEMORY_ALLOCATION_EXT waits happen while allocating memory from a query’s reserved grant. Watch it on columnstore ETL workloads. |
RESOURCE_GOVERNOR_IDLE | SQL Server RESOURCE_GOVERNOR_IDLE waits mean queries are being held idle by a Resource Governor CAP_CPU_PERCENT limit or a capped cloud service tier. |
RESOURCE_SEMAPHORE | Query memory grant queuing (pillar) |
RESOURCE_SEMAPHORE_MUTEX | SQL Server RESOURCE_SEMAPHORE_MUTEX waits guard the code that hands out query memory and threads. The real problem is the wait that follows it. |
RESOURCE_SEMAPHORE_QUERY_COMPILE | SQL Server RESOURCE_SEMAPHORE_QUERY_COMPILE waits show memory pressure during compilation, from concurrent complex compiles or cache churn. |
RESOURCE_SEMAPHORE_SMALL_QUERY | SQL Server RESOURCE_SEMAPHORE_SMALL_QUERY waits mean even small memory grants are queuing, a sign the main grant pool is jammed by big queries. |
RESTORE_MSDA_THREAD_BARRIER | SQL Server RESTORE_MSDA_THREAD_BARRIER waits sync threads restoring from multiple backup devices. Pre-2019 errors could hang restores at this barrier. |
RTDATA_LIST | SQL Server RTDATA_LIST waits guard runtime metrics for natively-compiled procedures. A known 2014 bug (KB 3007050) made it excessive; patch fixes it. |
SESSION_WAIT_STATS_CHILDREN | SQL Server SESSION_WAIT_STATS_CHILDREN waits synchronize updates to sys.dm_exec_session_wait_stats data for a session. Meta-bookkeeping; benign. |
SHUTDOWN | SQL Server SHUTDOWN waits mean a SHUTDOWN statement is waiting for active connections to finish. Find and kill the blocking sessions to proceed. |
SLEEP_BPOOL_FLUSH | SQL Server SLEEP_BPOOL_FLUSH waits mean checkpoint is pacing its writes to avoid flooding the disk. Benign unless a checkpoint is actually blocked. |
SLEEP_BPOOL_STEAL | SQL Server SLEEP_BPOOL_STEAL, SLEEP_BUFFERPOOL_HELPLW and SLEEP_MEMORYPOOL_ALLOCATEPAGES waits are free-list stalls, signs of buffer pool memory pressure. (family page) |
SLEEP_BUFFERPOOL_HELPLW | SQL Server SLEEP_BPOOL_STEAL, SLEEP_BUFFERPOOL_HELPLW and SLEEP_MEMORYPOOL_ALLOCATEPAGES waits are free-list stalls, signs of buffer pool memory pressure. (family page) |
SLEEP_DBSTARTUP | SQL Server SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP and SLEEP_MASTERDBREADY waits measure instance startup phases. SLEEP_DBSTARTUP equals database recovery time. (family page) |
SLEEP_DCOMSTARTUP | SQL Server SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP and SLEEP_MASTERDBREADY waits measure instance startup phases. SLEEP_DBSTARTUP equals database recovery time. (family page) |
SLEEP_MASTERDBREADY | SQL Server SLEEP_DBSTARTUP, SLEEP_DCOMSTARTUP and SLEEP_MASTERDBREADY waits measure instance startup phases. SLEEP_DBSTARTUP equals database recovery time. (family page) |
SLEEP_MEMORYPOOL_ALLOCATEPAGES | SQL Server SLEEP_BPOOL_STEAL, SLEEP_BUFFERPOOL_HELPLW and SLEEP_MEMORYPOOL_ALLOCATEPAGES waits are free-list stalls, signs of buffer pool memory pressure. (family page) |
SLEEP_TASK | SQL Server SLEEP_TASK waits are generic task sleeps, usually benign background noise, but on a live waiting task they can flag hash spills to tempdb. |
SNI_CRITICAL_SECTION | SQL Server SNI_CRITICAL_SECTION waits are threads synchronizing inside the SQL Server Network Interface while processing client packets. Benign. |
SNI_TASK_COMPLETION | SQL Server SNI_TASK_COMPLETION waits occur while tasks finish during a NUMA node state change, as new network listeners are created. Rare and benign. |
SOS_DISPATCHER_MUTEX | SQL Server SOS_DISPATCHER_MUTEX waits guard the dispatcher pool management code, including pool size adjustments. Internal machinery; benign. |
SOS_MEMORY_TOPLEVELBLOCKALLOCATOR | SQL Server SOS_MEMORY_TOPLEVELBLOCKALLOCATOR waits guard the allocator that steals memory from the buffer pool. VAS exhaustion is the known trigger. |
SOS_PHYS_PAGE_CACHE | SQL Server SOS_PHYS_PAGE_CACHE waits guard physical page allocation with locked pages in memory. Usually quiet; a NUMA bug spiked it on 2012/2014. |
SOS_SCHEDULER_YIELD | CPU scheduler pressure (pillar) |
SOS_SYNC_TASK_ENQUEUE_EVENT | SQL Server SOS_SYNC_TASK_ENQUEUE_EVENT waits occur when a task starts synchronously, with the starter waiting for it to begin. Rare and benign. |
SOS_WORKER_MIGRATION | SQL Server SOS_WORKER_MIGRATION waits track workers migrating between schedulers within a NUMA node, added with SQL Server 2019 balancing. Benign. |
SOS_WORK_DISPATCHER | SQL Server SOS_WORK_DISPATCHER waits are idle SQLOS threads waiting for work. Top wait on SQL Server 2019+ and Azure SQL, and safely ignored. |
SP_SERVER_DIAGNOSTICS_SLEEP | SQL Server SP_SERVER_DIAGNOSTICS_SLEEP waits are the system health monitor sleeping between sp_server_diagnostics runs. Benign; safely ignored. |
SQLCLR_APPDOMAIN | SQL Server SQLCLR_APPDOMAIN waits occur while CLR waits for an application domain to finish starting. Watch for repeated appdomain unload/reload cycles. |
SQLCLR_ASSEMBLY | SQL Server SQLCLR_ASSEMBLY waits are threads waiting for access to the loaded assembly list in an appdomain. Rarely significant; check load churn. |
SQLTRACE_FILE_BUFFER | SQL Server SQLTRACE_FILE_BUFFER, FILE_READ/WRITE_IO_COMPLETION and PENDING_BUFFER_WRITERS waits cover writing trace data to files. Usually benign. (family page) |
SQLTRACE_FILE_READ_IO_COMPLETION | SQL Server SQLTRACE_FILE_BUFFER, FILE_READ/WRITE_IO_COMPLETION and PENDING_BUFFER_WRITERS waits cover writing trace data to files. Usually benign. (family page) |
SQLTRACE_FILE_WRITE_IO_COMPLETION | SQL Server SQLTRACE_FILE_BUFFER, FILE_READ/WRITE_IO_COMPLETION and PENDING_BUFFER_WRITERS waits cover writing trace data to files. Usually benign. (family page) |
SQLTRACE_INCREMENTAL_FLUSH_SLEEP | SQL Server SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits are the trace writer sleeping between flushes to the default trace file. Benign; filter it out. |
SQLTRACE_PENDING_BUFFER_WRITERS | SQL Server SQLTRACE_FILE_BUFFER, FILE_READ/WRITE_IO_COMPLETION and PENDING_BUFFER_WRITERS waits cover writing trace data to files. Usually benign. (family page) |
TERMINATE_LISTENER | SQL Server TERMINATE_LISTENER waits occur while a network (SNI) listener is destroyed, during shutdowns and network reconfigurations. Benign marker. |
THREADPOOL | Worker thread exhaustion, treat as emergency (pillar) |
TRACEWRITE | SQL Server TRACEWRITE waits mean SQL Trace is waiting on trace buffers, usually a live Profiler session or heavy server-side trace slowing things. |
TRACE_EVTNOTIF | SQL Server TRACE_EVTNOTIF waits occur once per fired event notification. High-frequency audit events like schema access checks drive the volume. |
UCS_SESSION_REGISTRATION | SQL Server UCS_SESSION_REGISTRATION waits guard the list of Service Broker sessions during add and remove operations. Trivial and benign. |
VDI_CLIENT_OTHER | SQL Server VDI_CLIENT_OTHER waits come from automatic seeding threads waiting for work, and the threads persist until restart after seeding completes. |
WAITFOR | SQL Server WAITFOR waits are sessions running WAITFOR DELAY or TIME statements. User-initiated by definition; big totals mean code is sleeping on purpose. |
WAITFOR_PER_QUEUE | SQL Server WAITFOR_PER_QUEUE waits are Service Broker workers waiting on WAITFOR RECEIVE against a specific queue in activation. Normal Broker idling. |
WAIT_ON_SYNC_STATISTICS_REFRESH | SQL Server WAIT_ON_SYNC_STATISTICS_REFRESH waits mean queries are stalled waiting for synchronous statistics updates. Async stats usually fix it. |
WAIT_XTP_CKPT_CLOSE | SQL Server WAIT_XTP_CKPT_CLOSE waits are threads waiting for an In-Memory OLTP checkpoint to complete. Benign XTP machinery; filter it out. |
WAIT_XTP_HOST_WAIT | SQL Server WAIT_XTP_HOST_WAIT waits are In-Memory OLTP operations started by the database engine and implemented by the host. Benign; filter it out. |
WAIT_XTP_OFFLINE_CKPT_LOG_IO | SQL Server WAIT_XTP_OFFLINE_CKPT_LOG_IO waits are In-Memory OLTP checkpoint threads waiting on log read I/O. Slow log storage stretches them. |
WAIT_XTP_OFFLINE_CKPT_NEW_LOG | SQL Server WAIT_XTP_OFFLINE_CKPT_NEW_LOG waits are In-Memory OLTP checkpoint threads waiting for new log to scan. Expected wherever XTP is enabled. |
WAIT_XTP_RECOVERY | SQL Server WAIT_XTP_RECOVERY waits mean database recovery is waiting for memory-optimized objects to load. The database stays offline until it finishes. |
WAIT_XTP_TASK_SHUTDOWN | SQL Server WAIT_XTP_TASK_SHUTDOWN waits occur while waiting for an In-Memory OLTP thread to complete and shut down. Benign lifecycle machinery. |
WRITELOG | Transaction log write latency at commit (pillar) |
WRITE_COMPLETION | SQL Server WRITE_COMPLETION waits show sessions waiting for write operations to finish, often under storage pressure or heavy concurrent write activity. |
XE_BUFFERMGR_ALLPROCESSED_EVENT | SQL Server XE_BUFFERMGR_ALLPROCESSED_EVENT waits occur while Extended Events session buffers flush to their targets on a background thread. Benign. |
XE_DISPATCHER_WAIT | SQL Server XE_DISPATCHER_WAIT waits are Extended Events dispatcher threads waiting for event buffers to process. Benign; filter out of analysis. |
XE_FILE_TARGET_TVF | SQL Server XE_FILE_TARGET_TVF waits occur while queries read Extended Events file targets via sys.fn_xe_file_target_read_file. Tracks your own reads. |
XE_LIVE_TARGET_TVF | SQL Server XE_LIVE_TARGET_TVF waits appear while someone watches an Extended Events live data stream, usually in SSMS. Benign; close idle viewers. |
XE_TIMER_EVENT | SQL Server XE_TIMER_EVENT waits are Extended Events dispatch timers implementing MAX_DISPATCH_LATENCY. Benign background noise, filter it out. |
XE_TIMER_MUTEX | SQL Server XE_TIMER_MUTEX waits guard the Extended Events engine’s timer structures, like dispatch latency timers. Internal machinery; benign. |
XTP_PREEMPTIVE_TASK | SQL Server XTP_PREEMPTIVE_TASK waits are generic In-Memory OLTP background workers running preemptively. Watch the Telemetry XE session edge case. |
SQL Server exposes around 1,200 wait types across different versions, although only a small percentage are commonly encountered in production. This library documents the waits that are useful for troubleshooting, groups related waits into family pages where appropriate, and provides deep-dive guides for the most important performance bottlenecks.
If you encounter a wait type that isn’t documented here, it’s usually either an internal engine wait or one that’s rarely actionable. In those cases, start by checking Microsoft’s official wait type documentation, your SQL Server version’s release notes, and whether the wait consistently appears near the top of your workload. As the library grows, additional wait types will continue to be added.
Leave a Reply