SQL Server 2005 Wait Types and correlation to other Performance information

Wait Type	Category	In Wait stats Table	Description	Correlation to Other information
ASYNC_DISKPOOL_LOCK	IO	True	Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file. SQL Server 2000: During Backup and Restore (for example, including zeroing out pages) threads written in parallel. SQL Server 2005: no longer initializes (for example, zeros out) data files before a restore.	Possible disk bottleneck. See the disk performance counters for confirmation.
ASYNC_IO_COMPLETION	IO	True	Occurs when a task is waiting for asynchronous I/Os to finish. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN. Any of the following reduces these waits: 1.    Adding additional IO bandwidth. 1.    Balancing IO across other drives. 2.    Reducing IO with appropriate indexing. 3.    Check for bad query plans. 4.    Check for memory pressure.	See section titled “Memory pressure and Disk IO subsystem issues” See PERFMON Physical Disk performance counters: 1.    Disk sec/read 2.    Disk sec/write 3.    Disk queues See PERFMON SQLServer:Buffer Manager performance counters for memory pressure: 1.    Page Life Expectancy 2.    Checkpoint pages/sec 3.    Lazy writes/sec See PERFMON SQLServer:Access Methods for correct indexing: 1.    Full Scans/sec 2.    Index seeks/sec SQL Profiler can be used to identify which Transact-SQL statements do scans. Select the scans event class and events scan:started and scan:completed. Include the object Id data column. Save the profiler trace to a trace table, and then search for the scans event. The scan:completed event provides associated IO so that you can also search for high reads, writes, and duration. Check SHOWPLAN for bad query plans
ASYNC_NETWORK_IO New	Network	True	Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from SQL Server.	Check network adapter bandwidth. 1 Gigabit is better than 100 megabits. 100 megabits is better than 10 megabits.
BACKUP New	Backup	True	Occurs when a task is blocked as part of backup processing.
BACKUP_CLIENTLOCK New	Backup	True	Internal Only.
BACKUP_OPERATOR New	Backup	True	Occurs 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 can indicate a hardware problem with the tape drive.	Check backup tape drive.
BACKUPBUFFER New	Backup	True	Occurs 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.	Check backup tape drive.
BACKUPIO	Backup	True	Occurs 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.	Check backup tape drive.
BACKUPTHREAD	Backup	True	Occurs when a task is waiting for a backup task to finish. Wait times can 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_PROCESS	Memory	True	Occurs when the background suspect page logger is trying to avoid running more than every five seconds which occurs when many suspect pages are encountered.	Suspect pages are captured in the msdb database system table dbo.suspect_pages. Suspect pages can be restored using online page level restore.
BROKER_CONNECTION_RECEIVE_TASK New	Service Broker	False	Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.
BROKER_ENDPOINT_STATE_MUTEX New	Service Broker	False	Occurs when there is contention to access the state of a service broker connection endpoint. Access to the state for changes is serialized.
BROKER_EVENTHANDLER New	Service Broker	False	Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.
BROKER_INIT New	Service Broker	False	Occurs when initializing Service Broker in each active database. This should rarely occur.
BROKER_MASTERSTART New	Service Broker	False	Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.
BROKER_RECEIVE_WAITFOR New	Service Broker	True	Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
BROKER_REGISTERALLENDPOINTS New	Service Broker	False	Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.
BROKER_SHUTDOWN	Service Broker	False	Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.
BROKER_TRANSMITTER New	Service Broker	False	Occurs when the Service Broker message transmitter is waiting for work to do.
BUILTIN_HASHKEY_MUTEX New		True	Can occur after instance startup when internal datastructures are initialized. Does not reoccur after datastructures have been initialized.
CHECKPOINT_QUEUE		False	Occurs while the checkpoint task is waiting for the next checkpoint request.	Checkpoint writes out dirty (for example, changed) data and log pages. Check for disk issues. See PERFMON Physical Disk performance counters
CHKPT		True	Occurs at server startup to tell the checkpoint thread that it can start
CLR_AUTO_EVENT New	CLR	True	Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated.
CLR_CRST New	CLR	True	Occurs 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_JOIN New	CLR	True	Occurs 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_EVENT New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.
CLR_MONITOR New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.
CLR_RWLOCK_READER New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a reader lock.
CLR_RWLOCK_WRITER New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a writer lock.
CLR_SEMAPHORE New	CLR	True	Occurs when a task is currently performing CLR execution and is waiting for a semaphore.
CLR_TASK_START New	CLR	False	Occurs while waiting for a CLR task to complete startup.
CMEMTHREAD	Memory	True	Occurs when a task is waiting for 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.	The serialization makes sure that as long as the users are allocating or freeing the memory from the memory object, any other server process IDs (SPIDs) that are trying to perform the same task have to wait, and the CMEMTHREAD waittype is set when the SPIDs are waiting. You might notice this waittype in many scenarios. However, this waittype is most frequently logged when the ad hoc query plans are being quickly inserted into a procedure cache from many different connections to the instance of SQL Server. You can address this bottleneck by limiting the data that must be inserted or removed from the procedure cache, such as explicitly parameterizing the queries so that the queries can be reused or using stored procedures where appropriate.
CURSOR		True	Asynch Cursor thread.
CURSOR_ASYNC		True	Internal only.
CXPACKET		True	Occurs when trying to synchronize the query processor exchange iterator. Consider lowering the degree of parallelism if contention on this wait type becomes a problem. Parallel process waits can sometimes occur when data is skewed. In such cases, one parallel thread may process a larger number of rows while another may process a smaller number of rows and so on. In an OLTP environment, excessive CXPACKET waits can affect the throughput of other OLTP traffic. In a data warehouse environment, CXPACKET waits are expected for multiple proc environments.	Check for parallelism: sp_Configure “max degree of parallelism”. If max degree of parallelism = 0, you might want to use one of the following options: 1.    turn off parallelism completely for OLTP workloads: set max degree of parallelism to 1 2.    limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
DBMIRROR_DBM_EVENT New	DBM	True	Internal only.
DBMIRROR_DBM_MUTEX New	DBM	True	Internal only.
DBMIRROR_EVENTS_QUEUE New	DBM	False	Occurs when database mirroring waits for events to process.
DBMIRROR_SEND New	DBM	True	Occurs 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_QUEUE New	DBM	False	Indicates that the database mirroring worker task is waiting for more work.
DBMIRRORING_CMD New	DBM	True	Occurs 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.
DBTABLE			Internal only. New Checkpoint request that is waiting for outstanding checkpoint request to complete	See SQL Buffer Manager performance counters: 1.    Page Life Expectancy 2.    Checkpoint pages/sec 3.    Lazy writes/sec
DEADLOCK_ENUM_MUTEX	Lock	True	Occurs 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_SEARCH	Lock	True	Large waiting time on this resource indicates that server is executing queries in addition to sys.dm_os_waiting_tasks and these queries are blocking deadlock monitor from running deadlock search (only one query or deadlock monitor can examine task state at any moment of time). DEADLOCK_TASK_SEARCH wait type is used by deadlock monitor only, queries in addition to sys.dm_os_waiting_tasks use wait type DEADLOCK_ENUM_MUTEX.
DEBUG		True	Occurs during Transact-SQL and CLR debugging for internal synchronization.
DISABLE_VERSIONING New		True	Occurs 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_SUSPEND	IO	True	Occurs 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 can indicate that the external backup is taking too much time to finish.
DLL_LOADING_MUTEX	XML	False	Occurs one time while waiting for the loading of the XML parser DLL.
DROPTEMP		True	Occurs between attempts to drop a temporary object if the previous try failed. The wait duration grows exponentially with each failed drop try.
DTC	DTC	True	Occurs when a task is waiting for 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. Waiting for Distributed Transaction Coordinator	Check transaction isolation level
DTC_ABORT_REQUEST	DTC	True	Occurs 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 waits for another session that is using the transaction
DTC_RESOLVE	DTC	True	Occurs 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_STATE	DTC	True	Occurs when a task is waiting for an event that protects changes to the internal MS DTC global state object. The state should be held for very short periods of time.
DTC_TMDOWN_REQUEST	DTC	True	Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First the worker waits 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 can continue until the connection with the MS DTC service has been reestablished.
DTC_WAITFOR_OUTCOME	DTC	True	Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.
DUMP_LOG_COORDINATOR		True	Occurs 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.
EC			Internal Only.
EE_PMOLOCK		True	Occurs during synchronization of certain memory allocation during statement execution.
EE_SPECPROC_MAP_INIT		False	Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the first accesses of the hash table after the SQL Server 2005 instance started.
ENABLE_VERSIONING		True	Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation enabled state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.
ERROR_REPORTING_MANAGER
EXCHANGE		True	Occurs during synchronization in the query processor exchange iterator during parallel queries.	Check for parallelism: sp_Configure “max degree of parallelism”. If max degree of parallelism = 0, you might want to use one of the following options: 1.    turn off parallelism completely: set max degree of parallelism to 1 2.    limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=4.
EXECSYNC		True	Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such area are bitmaps, large binary objects (BLOBs) and the spool iterator. LOBs can frequently use this wait state. Bitmap and spool use should not cause contention.
Failpoint		True	Internal only.
FCB_REPLICA_READ		True	Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.
FCB_REPLICA_WRITE		True	Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.
FT_RESTART_CRAWL		True	Occurs when a full-text crawl (population) must restart from a last known good point to recover from a transient failure. The wait is for letting the worker tasks currently working on that population to complete/exit the current step.
FT_RESUME_CRAWL		True	Occurs when throttled full-text crawls (population) pause to wait for existing activity to finish.
HTTP_ENDPOINT_COLLCREATE		True	Internal only.
HTTP_ENUMERATION		True	Occurs at startup to enumerate the HTTP endpoints to start HTTP.
IMP_IMPORT_MUTEX		True	Internal only.
IMPPROV_IOWAIT		True	Occurs when SQL Server waits for a bulkload I/O to finish.
INDEX_USAGE_STATS_MUTEX		True	Internal only.
IO_AUDIT_MUTEX		True	Occurs during synchronization of trace event buffers.
IO_COMPLETION		True	Occurs while waiting for I/O operations to finish. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits. Identify disk bottlenecks by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN Any of the following reduces these waits: 1.    Adding additional IO bandwidth, 2.    Balancing IO across other drives 3.    Reducing IO with appropriate indexing 4.    Check for bad query plans	See Disk performance counters: 1.    Disk sec/read 2.    Disk sec/write 3.    Disk queues See SQL Buffer Manager performance counters: 1.    Page Life Expectancy 2.    Checkpoint pages/sec 3.    Lazy writes/sec See SQL Access Methods for  correct indexing: 1.    Full Scans/sec 2.    Index seeks/sec See memory performance counter ·         Page faults/sec Refer to Io_stalls section to identify IO bottlenecks. SQL Profiler can be used to identify which Transact-SQL statements do scan. Select the scans event class and events scan:started and scan:completed. Include the object Id data column. Save the profiler trace to a trace table, and then search for the scans event. The scan:completed event provides the associated IO so that you can also search for high reads, writes, and duration. Check SHOWPLAN for bad query plans
KTM_ENLISTMENT		True	Internal Only.
KTM_RECOVERY_MANAGER		True	Internal Only.
KTM_RECOVERY_RESOLUTION		True	Internal Only.
LATCH_x			Latches are short term light weight synchronization objects. Latches are not held for the duration of a transaction. “Plain” latches are generally not related to IO. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages (PAGELATCH_x is used for that). Possibly the most common case is contention on internal caches (not the buffer pool pages), especially when using heaps or text.	If high, check PERFMON for 1.    memory pressure 2.    SQL Latch waits (ms) Look for LOG and Pagelatch_UP wait types. Latch_x waits can frequently be reduced by solving LOG and PAGELATCH_UP contention. If there is no  LOG or PAGELATCH_UP contention, the only other option is to partition the table/index in question in order to create multiple caches (the caches are per-index).
LATCH_DT		True	Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats group LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX and LATCH_DT waits together.	See LATCH_x
LATCH_EX		True	Occurs when waiting for a EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A breakdown of LATCH_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.	See LATCH_x
LATCH_KP		True	Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A break of latch_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.	See LATCH_x
LATCH_NL		True	Internal Only.	See LATCH_x
LATCH_SH		True	Occurs when waiting for a SH (share) latch. This does not include buffer latches or transaction mark latches. A break of latch_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.	See LATCH_x
LATCH_UP		True	Occurs when waiting for a UP (update) latch. This does not include buffer latches or transaction mark latches. A break of latch_* waits is available in sys.dm_os_latch_stats. Notice that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.	See LATCH_x
LAZYWRITER_SLEEP		True	Occurs when lazy writer tasks are suspended. In a measure of the time that is spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.
LCK_x			Possible transaction management issue. 1.    For shared locks, check Isolation level for transaction. 2.    Keep transaction as short as possible	See SQL Locks performance counters ·         Lock wait time (ms) Hint: check for memory pressure, which causes more physical IO, therefore prolonging the duration of transactions and locks.
LCK_M_BU			Occurs when a task is waiting to acquire a Bulk update lock. See the sys.dm_tran_locks topc for a lock compatibility matrix.	See Lck_x
LCK_M_IS			Occurs when a task is waiting to acquire an Intend Share lock. See the sys.dm_tran_locks topc for a lock compatibility matrix.	See Lck_x
LCK_M_IU			Occurs when a task is waiting to acquire an Intend Update lock. See the sys.dm_tran_locks topc for a lock compatibility matrix.	See Lck_x
LCK_M_IX			Occurs when a task is waiting to acquire an Intent Exclusive lock. See the sys.dm_tran_locks topc for a lock compatibility matrix.	See Lck_x
LCK_M_RIn_NL			Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RIn_S			Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RIn_U			Occurs when a task is waiting to acquire an Update lock on the current key value and an insert range lock between the current and previous key. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RIn_X		True	Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RS_S		True	Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RS_U		True	Occurs when a task is waiting to acquire a Update Range lock on the current key value and a shared range lock between the current and previous key. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RX_S		True	Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RX_U		True	Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_RX_X		True	Occurs 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. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_S		True	Occurs when a task is waiting to acquire a Shared lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_SCH_M		True	Occurs when a task is waiting to acquire a Schema Modify lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_SCH_S		True	Occurs when a task is waiting to acquire a Schema Modify lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_SIU		True	Occurs when a task is waiting to acquire a Shared With Intent Updated lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_SIX		True	Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_U		True	Occurs when a task is waiting to acquire an Update lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_UIX		True	Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LCK_M_X		True	Occurs when a task is waiting to acquire an Exclusive lock. See the sys.dm_tran_locks topic for a lock compatibility matrix.	See Lck_x
LOGBUFFER		True	Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values can indicate that the log devices cannot keep up with the logging information being generated by the server.	See Disk performance counters: 1.    Disk sec/read 2.    Disk sec/write 3.    Disk queues
LOGMGR		True	Occurs when a task is waiting for any outstanding log I/Os to finish before it shuts down the log. Identify disk bottlenecks, by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN Any of the following reduces these waits: 1.    Adding additional IO bandwidth, 2.    Balancing IO across other drives 3.    Moving / Isolating the transaction log on its own drive	See Disk performance counters: 1.    Disk sec/read 2.    Disk sec/write 3.    Disk queues See SQL Buffer Manager performance counters: 1.    Page Life Expectancy 2.    Checkpoint pages/sec 3.    Lazy writes/sec Check Io_stall for tranlog ·         select * from sys.dm_io_virtual_file_stats(dbid,file#)
LOGMGR_FLUSH		True	Internal Only.
LOGMGR_RESERVE_APPEND		True	Occurs when a task is waiting to see whether log truncation frees 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_QUEUE		True	Occurs while waiting for memory to be available for use.
MIRROR_SEND_MESSAGE		True	Internal Only.
MISCELLANEOUS		True	Catch all wait type
MSQL_DQ		True	Occurs 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_SYNC_PIPE		True	Internal Only.
MSQL_XACT_MGR_MUTEX		True	Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation
MSQL_XACT_MUTEX		True	Occurs during synchronization of usage of a transaction. A request must successfully acquire the mutex before it can use the transaction.
MSQL_XP		True	Occurs 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.
MSSEARCH		True	Occurs during Full-Text search calls. This wait ends when the full-text operation is finished. It does not indicate contention, but the duration of full-text operations.
NET_WAITFOR_PACKET		True	Occurs when a connection is waiting for a network packet during a network read.
OLEDB			Occurs when SQL Server calls the Microsoft SQL Native Client OLE DB Provider. This state is not used for synchronization, instead it indicates the duration of calls to the OLE DB provider. It can also include the following: Linked server calls including four part name calls, remote procedure calls, openquery, openrowset and so on. Queries that access DMVs, because these are implemented as OLE DB rowset providers. Heavy Profiler tracing	1.    Check placement of client applications including any file input read by the client and SQL Server data and log files. See PERFMON disk secs/read and disk secs/write. If disk secs/read are high, you can add IO bandwidth, balance IO across other drives, and move or isolate the database and transaction log to its own drives 2.    Inspect Transact-SQL code for RPC, Distributed (Linked Server) and Full Text Search. Although SQL Server supports these type queries, they are sometimes performance bottlenecks. 3.    To retrieve the SQL statement involved in OLE DB waits, refer to section “Retrieving statements in the waiter list”.
PAGEIOLATCH_x			Latches are short term synchronization objects. used to synchronize access to buffer pages. PageIOLatch is used for disk to memory transfers.	If this is significant in percentage, it typically suggests disk IO subsystem issues. Check disk counters.
PAGEIOLATCH_DT		True	Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits of this kind indicate a problem with the disk subsystem.	See PAGEIOLATCH_x
PAGEIOLATCH_EX		True	Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits of this kind indicate a problem with the disk subsystem.	See PAGEIOLATCH_x
PAGEIOLATCH_KP		True	Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits of this kind indicate a problem with the disk subsystem.	See PAGEIOLATCH_x
PAGEIOLATCH_NL		True	Internal Only.	See PAGEIOLATCH_x
PAGEIOLATCH_SH			Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits of this kind indicate a problem with the disk subsystem.	See PAGEIOLATCH_x
PAGEIOLATCH_UP			Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits of this kind indicate a problem with the disk subsystem.	See PAGEIOLATCH_x
PAGELATCH_x			Latches are short term light weight synchronization objects. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, and so on. Therefore, the duration of latches is typically sensitive to available memory.	If this is significant in percentage, it typically indicates cache contention.
PAGELATCH_DT		True	Occurs when a task is waiting for a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.	See PAGELATCH_x
PAGELATCH_EX		True	Occurs when a task is waiting for a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode. Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index range can cause this kind of contention. If many inserts must be added on the same page, they are serialized using the latch. Lots of inserts into the same range can also cause page splits in the index which holds onto the latch while allocating a new page (this can take time). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate index.	See PAGELATCH_x
PAGELATCH_KP		True	Occurs when a task is waiting for a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.	See PAGELATCH_x
PAGELATCH_NL		True	Internal Only.	See PAGELATCH_x
PAGELATCH_SH		True	Occurs when a task is waiting for a latch for a buffer that is not in an I/O request. The latch request is in Shared mode. Contention can be caused by issues other than IO or memory performance, for example, heavy concurrent inserts into the same index range can cause this kind of contention. If many inserts must be added on the same page, they are serialized using the latch. Lots of inserts into the same range can also cause page splits in the index which holds onto the latch while allocating a new page (this can take a time). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate	See PAGELATCH_x
PAGELATCH_UP		True	Occurs when a task is waiting for a latch for a buffer that is not in an I/O request. The latch request is in Update mode. Page latch Update is used only for allocation related pages, and contention on it is frequently a sign that more files are needed. With multiple files, allocations can be distributed across multiple files therefore reducing demand on the per-file data structures stored on these pages. The contention is not IO performance, but internal allocation contention to access the pages. Adding more spindles to a file or moving the file to a faster disk does not help, nor does adding more memory.	See PAGELATCH_x
PRINT_ROLLBACK_PROGRESS			Used 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).
QNMANAGER_ACQUIRE			Internal Only.
QPJOB_KILL			Indicates 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_ABORT			Indicates 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 finished. This is an ordinary but rare state, and should be very short. A good value is less than one second.
QRY_MEM_GRANT_INFO_MUTEX		True	Occurs 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 for this state. If this mutex is not released, all new memory-using queries will stop responding.
QUERY_NOTIFICATION_MGR_MUTEX		True	Occurs during synchronization of the garbage collection queue in the Query Notification Manager.
QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX		True	Occurs during state synchronization for transactions in Query Notifications.
QUERY_NOTIFICATION_TABLE_MGR_MUTEX		True	Occurs during internal synchronization within the Query Notification Manager.
QUERY_NOTIFICATION_UNITTEST_MUTEX		True	Internal Only.
QUERY_OPTIMIZER_PRINT_MUTEX		False	Occurs during synchronization of production of query optimizer diagnostic output. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.
QUERY_TRACEOUT		True	Internal Only.
RECOVER_CHANGEDB		True	Occurs during synchronization of database warm standby databases.
REPL_CACHE_ACCESS		True	Occurs during synchronization on a replications article cache. During these waits the replication log reader stalls and DDL on a published table is blocked.
REPL_SCHEMA_ACCESS		Yes	Occurs during synchronization on a replications article cache. During these waits the replication log reader stalls and DDL on a published table is blocked
REPLICA_WRITES		True	Occurs while a task waits for page writes to database snapshots or DBCC replicas to finish.
REQUEST_DISPENSER_PAUSE			Occurs 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.
RESOURCE_QUEUE			Occurs during synchronization on various internal resource queues.	Synchronization object
RESOURCE_SEMAPHORE		True	Occurs when a query memory request cannot be granted immediately because of other concurrent queries. High waits and wait times can indicate excessive number of concurrent queries or excessive memory request amount. COMMON for DSS like workload and large queries such as hash joins; must wait for memory quota (grant) before it is executed.	See SQL Memory Mgr performance counters 1.    Memory Grants Pending 2.    Memory Grants Outstanding
RESOURCE_SEMAPHORE_MUTEX		True	Occurs 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_COMPILE		True	Occurs when the number of concurrent query compiles hit a throttling limit in order to avoid over-burdening the system with compiles. High waits and wait times can indicate of excessive compilations, recompiles or uncachable plans.
RESOURCE_SEMAPHORE_SMALL_QUERY		True	Occurs when memory request by small query cannot be granted immediately because of other concurrent queries. Wait time should not exceed several seconds because the server transfers the request to the mainquery memory pool if it cannot grant the requested memory within a few seconds. High waits can indicate too many concurrent small queries when the main memory pool is blocked by waiting queries.
SEC_DROP_TEMP_KEY		True	Occurs after failed attempt to drop a temporary security key before a retry attempt.
SERVER_IDLE_CHECK		True	Occurs during synchronization of an instance of SQL Server idle status when a resource monitor is trying to declare an instance of SQL Server as idle or trying wake it up.
SLEEP_BPOOL_FLUSH		True	Occurs during checkpoints when checkpoint is throttling the issuing of new I/Os in order to avoid flooding the disk subsystem.
SLEEP_SYSTEMTASK		True	Occurs during start of background task while waiting for tempdb to complete startup.
SLEEP_TASK		True	Occurs when a task sleeps while waiting for a generic event to occur.
SNI_HTTP_ACCEPT		True	Internal Only.
SNI_HTTP_WAITFOR_0_DISCON		True	Occurs during SQL Server shutdown while waiting for outstanding http connections to exit.
SOAP_READ		True	Occurs when waiting for an HTTP network read to finish.
SOAP_WRITE		True	Occurs when waiting for an HTTP network write to finish.
SOS_CALLBACK_REMOVAL		True	Occurs when 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_LOCALALLOCATORLIST		True	Occurs during internal synchronization in the SQL Server memory manager.
SOS_OBJECT_STORE_DESTROY_MUTEX		True	Occurs during internal synchronization in memory pools when destroying objects from the pool
SOS_PROCESS_AFFINITY_MUTEX		True	Occurs during synchronizing of access to process affinity settings
SOS_RESERVEDMEMBLOCKLIST		True	Occurs during internal synchronization in the SQL Server memory manager.
SOS_SCHEDULER_YIELD		True	Occurs 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_STACKSTORE_INIT_MUTEX		True	Occurs during synchronization of internal store initialization.
SOS_SYNC_TASK_ENQUEUE_EVENT		True	Occurs when a task is started in a synchronous manner. Most tasks in SQL Server 2005 are started in an asynchronous manner and control returns to the starter immediately after the task request has been put on the work queue.
SOS_VIRTUALMEMORY_LOW		True	Occurs when a memory allocation waits for a resource manager to free virtual memory.
SOSHOST_EVENT	SOS	True	Occurs when a hosted component, such as CLR, waits for a SQL Server 2005 event synchronization object.
SOSHOST_INTERNAL	SOS	True	Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.
SOSHOST_MUTEX	SOS	True	Occurs when a hosted component, such as CLR, waits for a SQL Server 2005 mutex synchronization
SOSHOST_RWLOCK	SOS	True	Occurs when a hosted component, such as CLR, waits for a SQL Server 2005 reader-writer synchronization
SOSHOST_SEMAPHORE	SOS	True	Occurs when a hosted component, such as CLR, waits for a SQL Server 2005 semaphore synchronization object
SOSHOST_SLEEP	SOS	True	Occurs when a hosted task sleeps when waiting for a generic event to occur Hosted tasks are used by hosted components such as CLR.
SOSHOST_TRACELOCK	SOS	True	Occurs during synchronization of access to trace streams.
SOSHOST_WAITFORDONE	SOS	True	Occurs when a hosted component, such as CLR, waits for a task to finish.
SQLCLR_APPDOMAIN	CLR	True	Occurs while CLR waits for an application domain to complete startup
SQLCLR_ASSEMBLY	CLR	True	Occurs while waiting for access to the loaded assembly list in the sql appdomain
SQLCLR_DEADLOCK_DETECTION	CLR	True	Occurs while CLR waits for deadlock detection to finish.
SQLCLR_QUANTUM_PUNISHMENT	CLR	True	Occurs 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 greedy task on other tasks.
SQLSORT_NORMMUTEX		True	Occurs during internal synchronization when initializing internal sorting structures.
SQLSORT_SORTMUTEX		True	Occurs during internal synchronization when initializing internal sorting structures.
SQLTRACE_BUFFER_FLUSH		True	Occurs when the SQL Trace flush task pauses between flushes. This wait is expected and long waits do not indicate a problem
SQLTRACE_LOCK		True	Occurs during synchronization on trace buffers during a file trace.
SQLTRACE_SHUTDOWN		True	Occurs when a trace shutdown waits for outstanding trace events to finish
SQLTRACE_WAIT_ENTRIES		True	Occurs when a SQL Trace event queue waits for packets to arrive on the queue.
SRVPROC_SHUTDOWN		True	Occurs when the shutdown process waits for internal resources to be released to shutdown cleanly.
TEMPOBJ		True	Occurs when temporary object drops are synchronized. This wait is rare and only occurs if a task has requested exclusive access for temp table drops.
THREADPOOL		True	Occurs when a task is waiting for a worker to run on. This can indicate that the max worker setting is too low or that batch executions are taking unusually long therefore reducing the number of worker available to satisfy other batches.
TRAN_MARKLATCH_DT		True	Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions. Marked transaction enable restore to specific marked transactions.
TRAN_MARKLATCH_EX		True	Occurs when waiting for an exclusive mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions. Marked transaction enable restore to specific marked transactions.
TRAN_MARKLATCH_KP		True	Occurs when waiting for a keep mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions. Marked transactions enable restore to specific marked transactions.
TRAN_MARKLATCH_NL		True	Internal Only.
TRAN_MARKLATCH_SH		True	Occurs when waiting for a share mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions. Marked transactions enable restore to specific marked transactions.
TRAN_MARKLATCH_UP		True	Occurs when waiting for an update mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions. Marked transactions enable restore to specific marked transactions.
TRANSACTION_MUTEX		True	Occurs during synchronization of access to a transaction by multiple batches.
UTIL_PAGE_ALLOC		True	Occurs when transaction log scans wait for memory to be available during memory pressure.
VIEW_DEFINITION_MUTEX		True	Occurs during synchronization on access to cached view definitions.
WAIT_FOR_RESULTS		True	Occurs when waiting for a query notification to be triggered.
WAITFOR		True	Occurs because 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.	Inspect Transact-SQL code for “waitfor delay” statement
WORKTBL_DROP		True	Occurs when pausing before retrying after a failed worktable drop.
WRITELOG			Occurs when waiting for a log flush to finish. Common operations that cause log flushes are checkpoints and transaction commits. Identify disk bottlenecks, by using Performance Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN Any of the following reduces these waits: 1.    Adding additional IO bandwidth, 2.    Balancing IO across other drives 3.    Moving or Isolating the transaction log on its own drive	See Disk performance counters: 1.    Disk sec/read 2.    Disk sec/write 3.    Disk queues See SQL Buffer Manager counters: 1.    Page Life Expectancy 2.    Checkpoint pages/sec 3.    Lazy writes/sec Check Io_stall for tranlog ·         select * from sys.dm_io_virtual_file_stats(dbid,file#)
XACT_OWN_TRANSACTION		True	Occurs when waiting to acquire ownership of a transaction.
XACT_RECLAIM_SESSION		True	Occurs when waiting for the current owner of a session to release ownership of the session.
XACTLOCKINFO		True	Occurs during synchronization of access to a transaction's list of locks. In addition to the transaction itself, a transactions list of locks is accessed by operations such as deadlock detection and lock migration during page splits.
XACTWORKSPACE_MUTEX		True	Occurs during synchronization of defections from a transactions in addition to the transfer of database locks between enlist members of a transaction.

...Happy SQLing
Thanks for Visiting and Sharing your Views