TechNet
Export (0) Print
Expand All

sys.dm_db_wait_stats (Azure SQL Database)

 

THIS TOPIC APPLIES TO: noSQL Server yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about all the waits encountered by threads that executed during operation. You can use this aggregated view to diagnose performance issues with Azure SQL Database and also with specific queries and batches.

Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.

Column nameData typeDescription
wait_typenvarchar(60)Name of the wait type. For more information, see Types of Waits, later in this topic.
waiting_tasks_countbigintNumber of waits on this wait type. This counter is incremented at the start of each wait.
wait_time_msbigintTotal wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
max_wait_time_msbigintMaximum wait time on this wait type.
signal_wait_time_msbigintDifference between the time that the waiting thread was signaled and when it started running.
  • This dynamic management view displays data only for the current database.

  • This dynamic management view shows the time for waits that have completed. It does not show current waits.

  • Counters are reset to zero any time the database is moved or taken offline.

  • A SQL Server worker thread is not considered to be waiting if any of the following is true:

    • A resource becomes available.

    • A queue is nonempty.

    • An external process finishes.

  • These statistics are not persisted across SQL Database failover events, and all data are cumulative since the last time the statistics were reset.

Requires VIEW DATABASE STATE permission on the server.

Resource waits
Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects.

Queue waits
Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.

External waits
External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.

In SQL Server the wait-time counters are bigint values and therefore are not as prone to counter rollover as the equivalent counters in earlier versions of SQL Server.

The following table lists the wait types encountered by tasks.

Wait typeDescription
ABRIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
ASSEMBLY_LOADOccurs during exclusive access to assembly loading.
ASYNC_DISKPOOL_LOCKOccurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.
ASYNC_IO_COMPLETIONOccurs when a task is waiting for I/Os to finish.
ASYNC_NETWORK_IOOccurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.
AUDIT_GROUPCACHE_LOCKOccurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.
AUDIT_LOGINCACHE_LOCKOccurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit login audit action groups.
AUDIT_ON_DEMAND_TARGET_LOCKOccurs when there is a wait on a lock that is used to ensure single initialization of audit related Extended Event targets.
AUDIT_XE_SESSION_MGROccurs when there is a wait on a lock that is used to synchronize the starting and stopping of audit related Extended Events sessions.
BACKUPOccurs when a task is blocked as part of backup processing.
BACKUP_OPERATOROccurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.
BACKUPBUFFEROccurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.
BACKUPIOOccurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.
BACKUPTHREADOccurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.
BAD_PAGE_PROCESSOccurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.
BROKER_CONNECTION_RECEIVE_TASKOccurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.
BROKER_ENDPOINT_STATE_MUTEXOccurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.
BROKER_EVENTHANDLEROccurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.
BROKER_INITOccurs when initializing Service Broker in each active database. This should occur infrequently.
BROKER_MASTERSTARTOccurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.
BROKER_RECEIVE_WAITFOROccurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
BROKER_REGISTERALLENDPOINTSOccurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.
BROKER_SERVICEOccurs when the Service Broker destination list that is associated with a target service is updated or re-prioritized.
BROKER_SHUTDOWNOccurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.
BROKER_TASK_STOPOccurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand.
BROKER_TO_FLUSHOccurs when the Service Broker lazy flusher flushes the in-memory transmission objects to a work table.
BROKER_TRANSMITTEROccurs when the Service Broker transmitter is waiting for work.
BUILTIN_HASHKEY_MUTEXMay occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.
CHECK_PRINT_RECORDIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
CHECKPOINT_QUEUEOccurs while the checkpoint task is waiting for the next checkpoint request.
CHKPTOccurs at server startup to tell the checkpoint thread that it can start.
CLEAR_DBOccurs during operations that change the state of a database, such as opening or closing a database.
CLR_AUTO_EVENTOccurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.
CLR_CRSTOccurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.
CLR_JOINOccurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.
CLR_MANUAL_EVENTOccurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.
CLR_MEMORY_SPYOccurs during a wait on lock acquisition for a data structure that is used to record all virtual memory allocations that come from CLR. The data structure is locked to maintain its integrity if there is parallel access.
CLR_MONITOROccurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.
CLR_RWLOCK_READEROccurs when a task is currently performing CLR execution and is waiting for a reader lock.
CLR_RWLOCK_WRITEROccurs when a task is currently performing CLR execution and is waiting for a writer lock.
CLR_SEMAPHOREOccurs when a task is currently performing CLR execution and is waiting for a semaphore.
CLR_TASK_STARTOccurs while waiting for a CLR task to complete startup.
CLRHOST_STATE_ACCESSOccurs where there is a wait to acquire exclusive access to the CLR-hosting data structures. This wait type occurs while setting up or tearing down the CLR runtime.
CMEMTHREADOccurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.
CXPACKETOccurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.
CXROWSET_SYNCOccurs during a parallel range scan.
DAC_INITOccurs while the dedicated administrator connection is initializing.
DBMIRROR_DBM_EVENTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
DBMIRROR_DBM_MUTEXIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
DBMIRROR_EVENTS_QUEUEOccurs when database mirroring waits for events to process.
DBMIRROR_SENDOccurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.
DBMIRROR_WORKER_QUEUEIndicates that the database mirroring worker task is waiting for more work.
DBMIRRORING_CMDOccurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.
DEADLOCK_ENUM_MUTEXOccurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.
DEADLOCK_TASK_SEARCHLarge waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.
DEBUGOccurs during Transact-SQL and CLR debugging for internal synchronization.
DISABLE_VERSIONINGOccurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.
DISKIO_SUSPENDOccurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.
DISPATCHER_QUEUE_SEMAPHOREOccurs when a thread from the dispatcher pool is waiting for more work to process. The wait time for this wait type is expected to increase when the dispatcher is idle.
DLL_LOADING_MUTEXOccurs once while waiting for the XML parser DLL to load.
DROPTEMPOccurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.
DTCOccurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable.

This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.
DTC_ABORT_REQUESTOccurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.
DTC_RESOLVEOccurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.
DTC_STATEOccurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.
DTC_TMDOWN_REQUESTOccurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.
DTC_WAITFOR_OUTCOMEOccurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.
DUMP_LOG_COORDINATOROccurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.
DUMPTRIGGERIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
ECIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
EE_PMOLOCKOccurs during synchronization of certain types of memory allocations during statement execution.
EE_SPECPROC_MAP_INITOccurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.
ENABLE_VERSIONINGOccurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.
ERROR_REPORTING_MANAGEROccurs during synchronization of multiple concurrent error log initializations.
EXCHANGEOccurs during synchronization in the query processor exchange iterator during parallel queries.
EXECSYNCOccurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.
EXECUTION_PIPE_EVENT_INTERNALOccurs during synchronization between producer and consumer parts of batch execution that are submitted through the connection context.
FAILPOINTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
FCB_REPLICA_READOccurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.
FCB_REPLICA_WRITEOccurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.
FS_FC_RWLOCKOccurs when there is a wait by the FILESTREAM garbage collector to do either of the following:

Disable garbage collection (used by backup and restore).

Execute one cycle of the FILESTREAM garbage collector.
FS_GARBAGE_COLLECTOR_SHUTDOWNOccurs when the FILESTREAM garbage collector is waiting for cleanup tasks to be completed.
FS_HEADER_RWLOCKOccurs when there is a wait to acquire access to the FILESTREAM header of a FILESTREAM data container to either read or update contents in the FILESTREAM header file (Filestream.hdr).
FS_LOGTRUNC_RWLOCKOccurs when there is a wait to acquire access to FILESTREAM log truncation to do either of the following:

Temporarily disable FILESTREAM log (FSLOG) truncation (used by backup and restore).

Execute one cycle of FSLOG truncation.
FSA_FORCE_OWN_XACTOccurs when a FILESTREAM file I/O operation needs to bind to the associated transaction, but the transaction is currently owned by another session.
FSAGENTOccurs when a FILESTREAM file I/O operation is waiting for a FILESTREAM agent resource that is being used by another file I/O operation.
FSTR_CONFIG_MUTEXOccurs when there is a wait for another FILESTREAM feature reconfiguration to be completed.
FSTR_CONFIG_RWLOCKOccurs when there is a wait to serialize access to the FILESTREAM configuration parameters.
FT_METADATA_MUTEXDocumented for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_RESTART_CRAWLOccurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.
FULLTEXT GATHEREROccurs during synchronization of full-text operations.
GUARDIANIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
HTTP_ENUMERATIONOccurs at startup to enumerate the HTTP endpoints to start HTTP.
HTTP_STARTOccurs when a connection is waiting for HTTP to complete initialization.
IMPPROV_IOWAITOccurs when SQL Server waits for a bulkload I/O to finish.
INTERNAL_TESTINGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
IO_AUDIT_MUTEXOccurs during synchronization of trace event buffers.
IO_COMPLETIONOccurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
IO_QUEUE_LIMITOccurs when the asynchronous IO queue for the Azure SQL Database has too many IOs pending. Tasks trying to issue another IO are blocked on this wait type until the number of pending IOs drop below the threshold. The threshold is proportional to the DTUs assigned to the database.
IO_RETRYOccurs when an I/O operation such as a read or a write to disk fails because of insufficient resources, and is then retried.
IOAFF_RANGE_QUEUEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
KSOURCE_WAKEUPUsed by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.
KTM_ENLISTMENTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
KTM_RECOVERY_MANAGERIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
KTM_RECOVERY_RESOLUTIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LATCH_DTOccurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_EXOccurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_KPOccurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_NLIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LATCH_SHOccurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_UPOccurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LAZYWRITER_SLEEPOccurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.
LCK_M_BUOccurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_ISOccurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_IUOccurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_IXOccurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_NLOccurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_SOccurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_UTask is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_XOccurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RS_SOccurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RS_UOccurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_SOccurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_UOccurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_XOccurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SOccurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SCH_MOccurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SCH_SOccurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SIUOccurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SIXOccurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_UOccurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_UIXOccurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_XOccurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LOG_RATE_GOVERNOROccurs when DB is waiting for quota to write to the log.
LOGBUFFEROccurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.
LOGGENERATIONIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LOGMGROccurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.
LOGMGR_FLUSHIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LOGMGR_QUEUEOccurs while the log writer task waits for work requests.
LOGMGR_RESERVE_APPENDOccurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.
LOWFAIL_MEMMGR_QUEUEOccurs while waiting for memory to be available for use.
MSQL_DQOccurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.
MSQL_XACT_MGR_MUTEXOccurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.
MSQL_XACT_MUTEXOccurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.
MSQL_XPOccurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.
MSSEARCHOccurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.
NET_WAITFOR_PACKETOccurs when a connection is waiting for a network packet during a network read.
OLEDBOccurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.
ONDEMAND_TASK_QUEUEOccurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.
PAGEIOLATCH_DTOccurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_EXOccurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_KPOccurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_NLIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGEIOLATCH_SHOccurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_UPOccurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.
PAGELATCH_DTOccurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.
PAGELATCH_EXOccurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
PAGELATCH_KPOccurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.
PAGELATCH_NLIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGELATCH_SHOccurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.
PAGELATCH_UPOccurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.
PARALLEL_BACKUP_QUEUEOccurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.
PREEMPTIVE_ABRIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PREEMPTIVE_AUDIT_ACCESS_EVENTLOGOccurs when the SQL Server Operating System (SQLOS) scheduler switches to preemptive mode to write an audit event to the Windows event log.
PREEMPTIVE_AUDIT_ACCESS_SECLOGOccurs when the SQLOS scheduler switches to preemptive mode to write an audit event to the Windows Security log.
PREEMPTIVE_CLOSEBACKUPMEDIAOccurs when the SQLOS scheduler switches to preemptive mode to close backup media.
PREEMPTIVE_CLOSEBACKUPTAPEOccurs when the SQLOS scheduler switches to preemptive mode to close a tape backup device.
PREEMPTIVE_CLOSEBACKUPVDIDEVICEOccurs when the SQLOS scheduler switches to preemptive mode to close a virtual backup device.
PREEMPTIVE_CLUSAPI_CLUSTERRESOURCECONTROLOccurs when the SQLOS scheduler switches to preemptive mode to perform Windows failover cluster operations.
PREEMPTIVE_COM_COCREATEINSTANCEOccurs when the SQLOS scheduler switches to preemptive mode to create a COM object.
PREEMPTIVE_HADR_LEASE_MECHANISMAlways On Availability Groups lease manager scheduling for CSS diagnostics.
PREEMPTIVE_SOSTESTINGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PREEMPTIVE_STRESSDRIVERIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PREEMPTIVE_TESTINGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PREEMPTIVE_XETESTINGIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PRINT_ROLLBACK_PROGRESSUsed to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).
PWAIT_HADR_CHANGE_NOTIFIER_TERMINATION_SYNCOccurs when a background task is waiting for the termination of the background task that receives (via polling) Windows Server Failover Clustering notifications. Internal use only.
PWAIT_HADR_CLUSTER_INTEGRATIONAn append, replace, and/or remove operation is waiting to grab a write lock on an Always On internal list (such as a list of networks, network addresses, or availability group listeners). Internal use only.
PWAIT_HADR_OFFLINE_COMPLETEDAn Always On drop availability group operation is waiting for the target availability group to go offline before destroying Windows Server Failover Clustering objects.
PWAIT_HADR_ONLINE_COMPLETEDAn Always On create or failover availability group operation is waiting for the target availability group to come online.
PWAIT_HADR_POST_ONLINE_COMPLETEDAn Always On drop availability group operation is waiting for the termination of any background task that was scheduled as part of a previous command. For example, there may be a background task that is transitioning availability databases to the primary role. The DROP AVAILABILITY GROUP DDL must wait for this background task to terminate in order to avoid race conditions.
PWAIT_HADR_WORKITEM_COMPLETEDInternal wait by a thread waiting for an async work task to complete. This is an expected wait and is for CSS use.
PWAIT_MD_LOGIN_STATSOccurs during internal synchronization in metadata on login stats.
PWAIT_MD_RELATION_CACHEOccurs during internal synchronization in metadata on table or index.
PWAIT_MD_SERVER_CACHEOccurs during internal synchronization in metadata on linked servers.
PWAIT_MD_UPGRADE_CONFIGOccurs during internal synchronization in upgrading server wide configurations.
PWAIT_METADATA_LAZYCACHE_RWLOCkOccurs during internal synchronization in metadata cache along with iterating index or stats in a table.
QPJOB_KILLIndicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.
QPJOB_WAITFOR_ABORTIndicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.
QRY_MEM_GRANT_INFO_MUTEXOccurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.
QUERY_ERRHDL_SERVICE_DONEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
QUERY_EXECUTION_INDEX_SORT_EVENT_OPENOccurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.
QUERY_NOTIFICATION_MGR_MUTEXOccurs during synchronization of the garbage collection queue in the Query Notification Manager.
QUERY_NOTIFICATION_SUBSCRIPTION_MUTEXOccurs during state synchronization for transactions in Query Notifications.
QUERY_NOTIFICATION_TABLE_MGR_MUTEXOccurs during internal synchronization within the Query Notification Manager.
QUERY_NOTIFICATION_UNITTEST_MUTEXIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
QUERY_OPTIMIZER_PRINT_MUTEXOccurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.
QUERY_TRACEOUTIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
QUERY_WAIT_ERRHDL_SERVICEIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
RECOVER_CHANGEDBOccurs during synchronization of database status in warm standby database.
REPL_CACHE_ACCESSOccurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.
REPL_SCHEMA_ACCESSOccurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence.
REPLICA_WRITESOccurs while a task waits for completion of page writes to database snapshots or DBCC replicas.
REQUEST_DISPENSER_PAUSEOccurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.
REQUEST_FOR_DEADLOCK_SEARCHOccurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.
RESMGR_THROTTLEDOccurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.
RESOURCE_QUEUEOccurs during synchronization of various internal resource queues.
RESOURCE_SEMAPHOREOccurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.
RESOURCE_SEMAPHORE_MUTEXOccurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.
RESOURCE_SEMAPHORE_QUERY_COMPILEOccurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.
RESOURCE_SEMAPHORE_SMALL_QUERYOccurs when memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.
SE_REPL_CATCHUP_THROTTLEOccurs when the transaction is waiting for one of the database secondaries to make progress.
SE_REPL_COMMIT_ACKOccurs when the transaction is waiting for quorum commit acknowledgement from secondary replicas.
SE_REPL_COMMIT_TURNOccurs when the transaction is waiting for commit after receiving quorum commit acknowledgements.
SE_REPL_ROLLBACK_ACKOccurs when the transaction is waiting for quorum rollback acknowledgement from secondary replicas.
SE_REPL_SLOW_SECONDARY_THROTTLEOccurs when the thread is waiting for one of the database secondary replicas.
SEC_DROP_TEMP_KEYOccurs after a failed attempt to drop a temporary security key before a retry attempt.
SECURITY_MUTEXOccurs when there is a wait for mutexes that control access to the global list of Extensible Key Management (EKM) cryptographic providers and the session-scoped list of EKM sessions.
SEQUENTIAL_GUIDOccurs while a new sequential GUID is being obtained.
SERVER_IDLE_CHECKOccurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.
SHUTDOWNOccurs while a shutdown statement waits for active connections to exit.
SLEEP_BPOOL_FLUSHOccurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.
SLEEP_DBSTARTUPOccurs during database startup while waiting for all databases to recover.
SLEEP_DCOMSTARTUPOccurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.
SLEEP_MSDBSTARTUPOccurs when SQL Trace waits for the msdb database to complete startup.
SLEEP_SYSTEMTASKOccurs during the start of a background task while waiting for tempdb to complete startup.
SLEEP_TASKOccurs when a task sleeps while waiting for a generic event to occur.
SLEEP_TEMPDBSTARTUPOccurs while a task waits for tempdb to complete startup.
SNI_CRITICAL_SECTIONOccurs during internal synchronization within SQL Server networking components.
SNI_HTTP_WAITFOR_0_DISCONOccurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.
SNI_LISTENER_ACCESSOccurs while waiting for non-uniform memory access (NUMA) nodes to update state change. Access to state change is serialized.
SNI_TASK_COMPLETIONOccurs when there is a wait for all tasks to finish during a NUMA node state change.
SOAP_READOccurs while waiting for an HTTP network read to complete.
SOAP_WRITEOccurs while waiting for an HTTP network write to complete.
SOS_CALLBACK_REMOVALOccurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.
SOS_DISPATCHER_MUTEXOccurs during internal synchronization of the dispatcher pool. This includes when the pool is being adjusted.
SOS_LOCALALLOCATORLISTOccurs during internal synchronization in the SQL Server memory manager.
SOS_MEMORY_USAGE_ADJUSTMENTOccurs when memory usage is being adjusted among pools.
SOS_OBJECT_STORE_DESTROY_MUTEXOccurs during internal synchronization in memory pools when destroying objects from the pool.
SOS_PROCESS_AFFINITY_MUTEXOccurs during synchronizing of access to process affinity settings.
SOS_RESERVEDMEMBLOCKLISTOccurs during internal synchronization in the SQL Server memory manager.
SOS_SCHEDULER_YIELDOccurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.
SOS_SMALL_PAGE_ALLOCOccurs during the allocation and freeing of memory that is managed by some memory objects.
SOS_STACKSTORE_INIT_MUTEXOccurs during synchronization of internal store initialization.
SOS_SYNC_TASK_ENQUEUE_EVENTOccurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.
SOS_VIRTUALMEMORY_LOWOccurs when a memory allocation waits for a resource manager to free up virtual memory.
SOSHOST_EVENTOccurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.
SOSHOST_INTERNALOccurs during synchronization of memory manager callbacks used by hosted components, such as CLR.
SOSHOST_MUTEXOccurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.
SOSHOST_RWLOCKOccurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.
SOSHOST_SEMAPHOREOccurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.
SOSHOST_SLEEPOccurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.
SOSHOST_TRACELOCKOccurs during synchronization of access to trace streams.
SOSHOST_WAITFORDONEOccurs when a hosted component, such as CLR, waits for a task to complete.
SQLCLR_APPDOMAINOccurs while CLR waits for an application domain to complete startup.
SQLCLR_ASSEMBLYOccurs while waiting for access to the loaded assembly list in the appdomain.
SQLCLR_DEADLOCK_DETECTIONOccurs while CLR waits for deadlock detection to complete.
SQLCLR_QUANTUM_PUNISHMENTOccurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.
SQLSORT_NORMMUTEXOccurs during internal synchronization, while initializing internal sorting structures.
SQLSORT_SORTMUTEXOccurs during internal synchronization, while initializing internal sorting structures.
SQLTRACE_BUFFER_FLUSHOccurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.
SQLTRACE_LOCKOccurs during synchronization on trace buffers during a file trace.
SQLTRACE_SHUTDOWNOccurs while trace shutdown waits for outstanding trace events to complete.
SQLTRACE_WAIT_ENTRIESOccurs while a SQL Trace event queue waits for packets to arrive on the queue.
SRVPROC_SHUTDOWNOccurs while the shutdown process waits for internal resources to be released to shutdown cleanly.
TEMPOBJOccurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.
THREADPOOLOccurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.
TIMEPRIV_TIMEPERIODOccurs during internal synchronization of the Extended Events timer.
TRACEWRITEOccurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.
TRAN_MARKLATCH_DTOccurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_EXOccurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_KPOccurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_NLIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
TRAN_MARKLATCH_SHOccurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_UPOccurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRANSACTION_MUTEXOccurs during synchronization of access to a transaction by multiple batches.
UTIL_PAGE_ALLOCOccurs when transaction log scans wait for memory to be available during memory pressure.
VIA_ACCEPTOccurs when a Virtual Interface Adapter (VIA) provider connection is completed during startup.
VIEW_DEFINITION_MUTEXOccurs during synchronization on access to cached view definitions.
WAIT_FOR_RESULTSOccurs when waiting for a query notification to be triggered.
WAITFOROccurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.
WAITFOR_TASKSHUTDOWNIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
WAITSTAT_MUTEXOccurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.
WCCIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
WORKTBL_DROPOccurs while pausing before retrying, after a failed worktable drop.
WRITE_COMPLETIONOccurs when a write operation is in progress.
WRITELOGOccurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.
XACT_OWN_TRANSACTIONOccurs while waiting to acquire ownership of a transaction.
XACT_RECLAIM_SESSIONOccurs while waiting for the current owner of a session to release ownership of the session.
XACTLOCKINFOOccurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.
XACTWORKSPACE_MUTEXOccurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.
XE_BUFFERMGR_ALLPROCESSED_EVENTOccurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.
XE_BUFFERMGR_FREEBUF_EVENTOccurs when either of the following conditions is true:

An Extended Events session is configured for no event loss, and all buffers in the session are currently full. This can indicate that the buffers for an Extended Events session are too small, or should be partitioned.

Audits experience a delay. This can indicate a disk bottleneck on the drive where the audits are written.
XE_DISPATCHER_CONFIG_SESSION_LISTOccurs when an Extended Events session that is using asynchronous targets is started or stopped. This wait indicates either of the following:

An Extended Events session is registering with a background thread pool.

The background thread pool is calculating the required number of threads based on current load.
XE_DISPATCHER_JOINOccurs when a background thread that is used for Extended Events sessions is terminating.
XE_DISPATCHER_WAITOccurs when a background thread that is used for Extended Events sessions is waiting for event buffers to process.
XE_MODULEMGR_SYNCIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
XE_OLS_LOCKIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
XE_PACKAGE_LOCK_BACKOFFIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_COMPROWSET_RWLOCKFull-text is waiting on fragment metadata operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_IFTS_RWLOCKFull-text is waiting on internal synchronization. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_IFTS_SCHEDULER_IDLE_WAITFull-text scheduler sleep wait type. The scheduler is idle.
FT_IFTSHC_MUTEXFull-text is waiting on an fdhost control operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_IFTSISM_MUTEXFull-text is waiting on communication operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.
FT_MASTER_MERGEFull-text is waiting on master merge operation. Documented for informational purposes only. Not supported. Future compatibility is not guaranteed.

Community Additions

ADD
Show:
© 2016 Microsoft