SQL Server wait type description

Wait type description
Async_diskpool_lock
This occurs when you attempt to synchronize parallel threads (execute tasks such as creating or initializing files.
 
Async_io_completion
This occurs when a task is waiting for I/O to complete.
 
Async_network_io
When a task is blocked after the network, it appears in network writing.
 
Backup
This occurs when a task is blocked as part of backup processing.
 
Backup_operator
The task appears when the task is waiting for tape loading. To view the tape status, query SYS. dm_io_backup_tapes. If the Mount operation is not suspended, the waiting type may indicate that the disk has hardware problems.
 
Backupbuffer
This occurs when the backup task is waiting for data or the buffer used to store data. This type is not common and only appears when the task is waiting to load the tape.
 
Backupio
This occurs when the backup task is waiting for data or the buffer used to store data. This type is not common and only appears when the task is waiting to load the tape.
 
Backupthread
This occurs when a task is waiting for the backup task to complete. The waiting time may be long, from several minutes to several hours. If the waiting task is in an I/O process, this type does not indicate a problem.
 
Bad_page_process
When the background error page recorder is trying to avoid running at more than five seconds.
 
Broker_receive_waitfor
It appears when receive waitfor is waiting. This status usually appears if no message is to be received.
 
Broker_transmitter
When SQL Server 2005 service broker transmission code has a very short wait time.
 
Broker_shutdown
Service brokers are closed as planned. The time for this status should be as short as possible.
 
Broker_masterstart
A task appears when it is waiting for the main event handler of the service broker to start. The time for this status to appear should be very short.
 
Broker_eventhandler
This occurs when a task is waiting in the active event handler of the service broker. The appearance time should be very short.
 
Broker_registerallendpoints
This occurs when the service broker connection endpoint is initialized. The appearance time should be very short.
 
Broker_init
It appears when the service broker in each active database is initialized. This status should not appear frequently.
 
Builtin_hashkey_mutex
For internal use only.
 
Chkpt
When the server is started, the checkpoint thread can be started.
 
Clr_auto_event
When a task is currently running in a common language (CLR) and is waiting for special automatic event to start.
 
Clr_crst
This occurs when a task is executing Clr and is waiting for the key part of the task currently being used by another task.
 
Clr_manual_event
This occurs when a task is executing Clr and is waiting for a specific manual event to start.
 
Clr_monitor
This occurs when a task is currently executing Clr and is waiting to obtain the lock for the monitor.
 
Clr_rwlock_reader
When a task is executing Clr and waiting for reader lock.
 
Clr_rwlock_writer
When a task is executing Clr and waiting for writer lock.
 
Clr_task_join
When a task is currently executing Clr and is waiting for another task to end. The waiting status appears when the two tasks are connected.
 
Clr_semaphore
When a task is executing Clr and waiting for the semaphore.
 
Cmemthread
This occurs when a task is waiting for a thread-Safe Memory Object. When multiple tasks attempt to allocate memory from the same memory object, the waiting time may be extended.
 
Cxpacket
This occurs when you try to synchronize the query processor exchange iterator. If the competition for this waiting type becomes a problem, you can reduce the degree of parallelism.
 
Deadlock_enum_mutex
When the deadlock monitor and SYS. dm_ OS _waiting_tasks attempt to ensure that multiple deadlock searches run at different times on SQL Server.
 
Deadlock_task_search
This occurs when a task is waiting for internal deadlock monitor synchronization. For this wait type event, the deadlock monitor is idle most of the time.
 
Disable_versioning
When SQL Server polls the version Transaction Manager to check whether the timestamp of the earliest active transaction is later than the timestamp when the status changes. If yes, all snapshot transactions started before the alter database statement runs have been completed. This wait state is used when SQL Server disables version control using the alter database statement.
 
Diskio_suspend
When a task is waiting for access to the file (external backup is active. This status is reported for each pending user process. A count greater than five for each user process may indicate that external backup takes too long to complete.
 
Droptemp
When a task is sleep when the connection is deadlocked. This task will wait before each (exponential) retry the delete operation.
 
DTC
This occurs when a task is waiting for an event for managing state transition. This State controls the time when the SQL server executes the MS DTC transaction recovery when it receives a notification that the Microsoft Distributed Transaction Processing Coordinator (MS DTC) service is unavailable.

This status also describes the tasks that wait when SQL server starts the ms dtc transaction commit and SQL Server is waiting for the MS DTC commit to complete.
 
Dtc_abort_request
Appears in a ms dtc worker thread session when it is waiting for ownership of the ms dtc transaction. When ms dtc has a transaction, the session can roll back the transaction. Generally, this session waits for another session that is using transactions.
 
Dtc_resolve
When the recovery task is waiting for the master database in a cross-database transaction to query the transaction result, it appears.
 
Dtc_state
An event occurs when a task is waiting for changes to the internal ms dtc global state object to be protected. This status should be kept very short.
 
Dtc_tmdown_request
Appears in the ms dtc worker thread session when SQL server receives a notification that the MS DTC service is unavailable. First, the worker thread will wait for the ms dtc recovery process to start. Then, the worker thread waits for the result of the distributed transaction being processed. This process may be performed until the connection to the ms dtc service is reestablished.
 
Dtc_waitfor_outcome
The recovery task appears when the recovery task waits for the ms dtc to be active to enable the solution for the prepared transaction.
 
Dump_log_coordinator
This occurs when the director is waiting for the subtask to generate data. This status usually does not appear. A long wait indicates an unexpected blocking. Subtasks should be investigated.
 
Enable_versioning
Before declaring that the database can be switched to the dbver_on state, SQL Server will wait until all the update transactions in the database are completed. This status is used when SQL server enables version control through the alter database statement.
 
Exchange
This occurs during the synchronization query processor exchange iterator. Rarely happen.
 
Execsync
This occurs during query processor synchronization in a region unrelated to the exchange iterator. Examples of such areas include bitmaps, large binary objects (LOB), and dummy offline iterators. Lob may often use this waiting status. Bitmap and offline use should not cause contention.
 
Fcb_replica_read
When a sparse file is read from a synchronized snapshot (or temporary snapshot created by DBCC.
 
Fcb_replica_write
This occurs when page push or page request of sparse files is synchronized (or temporary snapshots created by DBCC.
 
Http_endpoint_collcreate
Create an endpoint set at startup.

Note:
This status is not specific to HTTP.
 

 
Http_enumeration
When it appears at startup, enumerate the HTTP endpoint to start HTTP.
 
Impprov_iowait
This occurs when SQL Server waits for bulkload I/O to complete.
 
Io_audit_mutex
For internal use only.
 
Latch_dt
Latch waits for Distributed Transaction latches. It does not include buffer latches or transaction tag latches.
 
Latch_ex
Lock wait for exclusive locks. Does not include the buffer or transaction tag latches.
 
Latch_kp
Lock wait for retaining locks. Does not include the buffer or transaction tag latches.
 
Latch_nl
Lock wait for an empty lock. Does not include the buffer or transaction tag latches.
 
Latch_sh
Lock wait for shared locks. Does not include the buffer or transaction tag latches.
 
Latch_up
Update the lock wait. Does not include the buffer or transaction tag latches.
 
Lazywriter_sleep
This occurs when the inert writer is suspended. Measure of the time used by a pending background task. Do not consider this status when searching for user barriers.
 
Lck_m_bu
This occurs when a task is waiting for a large-capacity update lock.
 
Lck_m_is lck_m_iu
This occurs when a task is waiting for an intent sharing lock.
 
Lck_m_iu
A task is waiting for an update lock.
 
Lck_m_ix
A task is waiting for an exclusive lock.
 
Lck_m_rin_nl
A task is waiting to obtain the null lock on the current key value and the insert range lock between the current key and the previous key. The null lock on the key refers to the lock immediately released.
 
Lck_m_rin_s
A task is waiting for the shared lock on the current key value and the insert range lock between the current key and the previous key.
 
Lck_m_rin_u
The task is waiting to obtain the update lock on the current key value and the insert range lock between the current key and the previous key.
 
Lck_m_rin_x
A task is waiting to obtain the exclusive lock on the current key value and the insert range lock between the current key and the previous key.
 
Lck_m_rs_s
A task is waiting for the shared lock on the current key value and the shared range lock between the current key and the previous key.
 
Lck_m_rs_u
A task is waiting to obtain the update lock on the current key value and the update range lock between the current key and the previous key.
 
Lck_m_rx_s
A task is waiting for the shared lock on the current key value and exclusive range lock between the current key and the previous key.
 
Lck_m_rx_u
A task is waiting to obtain the update lock on the current key value and the exclusive range lock between the current key and the previous key.
 
Lck_m_rx_x
A task is waiting to obtain the exclusive lock on the current key value and the exclusive range lock between the current key and the previous key.
 
Lck_m_s
This occurs when a task is waiting for the shared lock to be obtained.
 
Lck_m_sch_m
This occurs when a task is waiting for obtaining the schema modification lock.
 
Lck_m_sch_s
This occurs when a task is waiting to obtain the architecture share lock.
 
Lck_m_siu
This occurs when a task is waiting for an update lock of the Sharing intention.
 
Lck_m_six
When a task is waiting for a shared intention exclusive lock.
 
Lck_m_u
This occurs when a task is waiting for an update lock.
 
Lck_m_uix
This error occurs when a task is waiting for an exclusive lock to obtain the update intention.
 
Lck_m_x
A task appears when it is waiting for an exclusive lock.
 
Db1__send
This occurs when a task is waiting to clear the network layer's communication backlog so that messages can be sent. Indicates that the communication layer is starting to reload and affects the database image data throughput.

Important:
Microsoft support policies are not applicable to SQL Server 2005 database mirroring. According to the default settings, the database image is currently disabled, but this function can be enabled by using trace flag 1400 as the startup parameter. This function is only for evaluation. (For more information about Trace flags, see trace flags (TRANSACT-SQL ).) Do not use database images in the production environment. Microsoft support does not support databases or applications that use database images. In SQL Server 2005, database image documentation is for evaluation only. SQL Server 2005 support and upgrade document policies are not applicable to database image documentation.
 

 
Dbmirroring_cmd
This occurs when a task is waiting for the log record to be refreshed to the disk. The waiting status should be retained for a long time.
 
Logbuffer
This occurs when a task is waiting for the space in the log buffer to store log records. The continuous high value may indicate that the log device cannot keep up with the number of logs generated by the server.
 
Logmgr
When a task is waiting for any unfinished log I/O to be completed before closing the log.
 
Logmgr_flush
For internal use only.
 
Logmgr_reserve_append
When a task is waiting to check whether log truncation can release the log space so that the task can write new log records.
 
Lowfail_memmgr_queue
When a task is deleting a page from a queue that fails due to insufficient memory.
 
Msql_dq
This occurs when a task is waiting for the completion of the Distributed Query operation. This state is used to detect potential deadlocks of multiple active result sets (MARS) applications. The wait will end when the Distributed Query call is completed.
 
Msql_xact_mgr_mutex
A task appears when it is waiting to obtain the ownership of the session Transaction Manager to perform session-level transaction operations. The mutex is used to synchronize the use of the Transaction Manager objects of different threads in the same session.
 
Msql_xp
This occurs when a task is waiting for the end of the extended stored procedure. SQL Server uses this wait state to detect potential Mars application deadlocks. The wait will stop when the Extended Stored Procedure Call ends.
 
Oledb
This occurs when SQL Server calls the Microsoft SQL native client oledb access interface. This status is not used for synchronization.
 
Pageiolatch_dt
This occurs when the task waits for the buffer block latches in the I/O Request. The lock request is in "broken" mode.
 
Pageiolatch_ex
This occurs when the task waits for the buffer block latches in the I/O Request. The lock request is in "exclusive" mode.
 
Pageiolatch_kp
This occurs when the task waits for the buffer block latches in the I/O Request. The lock request is in "keep" mode.
 
Pageiolatch_nl
This occurs when the task waits for the buffer block latches in the I/O Request. The latch request is in "null" mode.
 
Pageiolatch_sh
This occurs when the task waits for the buffer block latches in the I/O Request. The latch request is in "shared" mode.
 
Pageiolatch_up
This occurs when the task waits for the buffer latches in the I/O Request. The latch request is in "Update" mode.
 
Pagelatch_dt
This occurs when the task waits for the buffer block latches in the I/O Request. The lock request is in "broken" mode.
 
Pagelatch_ex
This occurs when the task waits for the buffer latches that are not in the I/O Request. The lock request is in "exclusive" mode.
 
Pagelatch_kp
This occurs when the task waits for the buffer latches that are not in the I/O Request. The lock request is in "keep" mode.
 
Pagelatch_nl
This occurs when the task waits for the buffer latches that are not in the I/O Request. The latch request is in "null" mode.
 
Pagelatch_sh
This occurs when the task waits for the buffer latches that are not in the I/O Request. The latch request is in "shared" mode.
 
Pagelatch_up
This occurs when the task waits for the buffer latches that are not in the I/O Request. The latch request is in "Update" mode.
 
Print_rollback_progress
It is used to wait for the user process to end in the database that has completed the conversion through the alter database termination clause. For more information, see alter database (TRANSACT-SQL ).
 
Pwait_qpjob_waitfor_abort
Indicates that asynchronous statistics are automatically updated and canceled by calling the kill command at run time. The update has been completed, but has been suspended until the termination thread message coordination is complete. This is a common and rare State and should be very short. Normally, the value is less than one second.
 
Pwait_qpjob_kill
Indicates that the automatic update of asynchronous statistics is canceled by calling the kill command at the start of running. The terminating thread is suspended, waiting for it to start listening to the kill command. Normally, the value is less than one second.
 
Qry_mem_grant_info_mutex
This error occurs when the query execution memory management attempts to control access to the static authorization information list. This status lists the currently approved memory requests and information about the memory requests that are waiting. This status is a simple access control status. This status should never wait for a long time. If the mutex is not released, all new queries that occupy memory will stop responding.
 
Query_icationication_mgr_mutex
Protect the spam queues in the query notification manager.
 
Query_icationication_subscription_mutex
Controls the mutex of a transaction based on each subscription. This means that each subscription has a mutex and uses it to change its transaction status.
 
Query_icationication_table_mgr_mutex
For internal use only.
 
Query_icationication_unittest_mutex
For internal use only.
 
Sqltrace_buffer_flush
When a task is waiting for a background task to refresh the trace buffer to the disk every four seconds.