T-SQL Locking, Blocking and Waiting

Microsoft's reference on Lock Events: http://msdn.microsoft.com/en-us/library/ms177493.aspx Microsoft SQL Server 2005 Waits and Queues troubleshooting guide (the document is at the bottom): http://technet.microsoft.com/en-us/library/cc966413.aspx INF: Understanding and resolving SQL Server blocking problems (KB224453) SQL Dev's exhaustive list of wait types: http://www.sqldev.net/misc/waittypes.htm Wait Types:

Wait Type Name	Numeric Wait Type	Description
MISCELLANEOUS	0x00	Collection bucket for all unknown wait types, should be zero. In any case it does not represent any meaningful data.
LCK_M_SCH_S	0x01	Schema stability lock
LCK_M_SCH_M	0x02	Schema modification lock
LCK_M_S	0x03	Share lock
LCK_M_U	0x04	Update lock
LCK_M_X	0x05	Exclusive lock
LCK_M_IS	0x06	Intent-Share lock
LCK_M_IU	0x07	Intent-Update lock
LCK_M_IX	0x08	Intent-Exclusive lock
LCK_M_SIU	0x09	Shared intent to update lock
LCK_M_SIX	0x0A	Share-Intent-Exclusive lock
LCK_M_UIX	0x0B	Update-Intent-Exclusive lock
LCK_M_BU	0x0C	Bulk Update lock
LCK_M_RS_S	0x0D	Range-share-share lock
LCK_M_RS_U	0x0E	Range-share-Update lock
LCK_M_RIn_NL	0x0F	Range-Insert-NULL lock
LCK_M_RIn_S	0x10	Range-Insert-Shared lock
LCK_M_RIn_U	0x11	Range-Insert-Update lock
LCK_M_RIn_X	0x12	Range-Insert-Exclusive lock
LCK_M_RX_S	0x13	Range-exclusive-Shared lock
LCK_M_RX_U	0x14	Range-exclusive-update lock
LCK_M_RX_X	0x15	Range-exclusive-exclusive lock
GROUP	0x20	All types with 0x20 are now used for I/O COMPLETION
SLEEP	0x20	This waittype indicates that the SPID is waiting for a specified time and is a common state for the background threads that process the lazywrites, the checkpoints, or the server-side profiler trace events.
IO_COMPLETION	0x21	This waittype indicates that the SPID is waiting for the I/O requests to complete. When you notice this waittype for an SPID in the sysprocesses system table, you must identify the disk bottlenecks by using the performance monitor counters, profiler trace, the fn_virtualfilestats system table-valued function, and the SHOWPLAN option to analyze the query plans that correspond to the SPID. You can reduce this waittype by adding additional I/O bandwidth or balancing I/O across other drives. You can also reduce I/O by using indexing, look for bad query plans, and look for memory pressure.
ASYNC_IO_COMPLETION	0x22	This waittype indicates that the SPID is waiting for the asynchronous I/O requests to complete. Like the IO_COMPLETION waittype, this waittype also indicates an I/O bottleneck. You may see this waittype for the SPIDs during the long-running I/O-bound operations, such as BACKUP, CREATE DATABASE, ALTER DATABASE, or the database autogrow. This waittype may also indicate disk bottlenecks
GROUP	0x40	All type with 0x40 do not below to a specific group
RESOURCE_SEMAPHORE	0x40	This waittype indicates that the SPID is waiting on a resource. Here, the SPIDs generally wait to acquire the memory for the sorting or the hashing operation during the query execution. This waittype may also indicate that memory pressure exists in the visible part of the buffer pool. When an SPID is waiting and the RESOURCE_SEMAPHORE waittype is logged in the sysprocesses system table for the SPID, this may also indicate that there are many SPIDs that are waiting for query optimizations. You cannot differentiate whether the SPID is waiting for a query optimization or is waiting for a Memory object just by seeing the waittype. Therefore, you must run the DBCC Memorystatus and review the results of the performance monitor trace. Note You can monitor the SPIDs that are waiting in the query optimization queue by using the DBCC MEMORYSTATUS Transact-SQL statement. For additional information, click the following article number to view the article in the Microsoft Knowledge Base: 271624 Using DBCC MEMORYSTATUS to monitor SQL Server memory usage
DTC	0x41	This waittype indicates that the SPID is waiting on the Microsoft Distributed Transaction Coordinator (MS DTC) service.
OLEDB	0x42	This waittype indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This waittype may also indicate that the SPID is waiting for remote procedure calls or linked server queries to return the required data. The SPID may also be waiting for BULK INSERT commands or full-search queries to return the required data.
FAILPOINT	0x43
RESOURCE_QUEUE	0x44	This is an ordinary “idle” state for background threads in SQL Server.
ASYNC_DISKPOOL_LOCK	0x45	You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file.
UMS_THREAD	0x46	This waittype indicates that a batch has been received from a client application but that there are no worker threads that are available to service the request. If you consistently see 0x0046 waittypes for multiple SPIDs, there is a significant bottleneck elsewhere in the system that is using all the available worker threads. Note that the waittime column is always 0 for the UMSTHREAD waittype, and the lastwaittype column may erroneously show the name of a different waittype instead of UMSTHREAD."
PIPELINE_INDEX_STAT	0x47	// All types with values between PWAIT_PIPELINE_BASE and // PWAIT_LAST_PIPELINE_BASE are now used for PIPLINE IDs. Each of these wait types // directly correlate to an enum type PipelineId, which is used by class // PipelineRequest. If a new PiplineId is added, then a new wait type must also // be added here. //
PIPELINE_LOG	0x48
PIPELINE_VLM	0x49
GROUP	0x80	All types with 0x80 bit set relate to DBTABLE type of waits
WRITELOG	0x81	This waittype indicates that the SPID is waiting for a transaction log I/O request to complete. This waittype may also indicate a possible disk bottleneck (Waiting on a writelog )
LOGBUFFER	0x82	Waiting on a free buffer
GROUP	0x100	All types with 0x100 bit set is waiting for a pss
PSS_CHILD	0x101	These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
GROUP	0x200	All types with 0x200 bit set are using upwait ()
EXCHANGE	0x200	These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
XCB
DBTABLE	0x202	This waittype indicates that a thread is waiting to perform a checkpoint and another thread is already checkpointing the database.
EC	0x203	This waittype indicates that the SPID is waiting for access to execution context.
TEMPOBJ	0x204	This waittype indicates that the SPID is waiting to drop a temporary object that is still being used.
XACTLOCKINFO	0x205	This waittype indicates that the SPID is waiting to perform maintenance on its lock list.
LOGMGR	0x206	This waittype is used when the SPID tries to shut down a database and waits for the pending transaction log I/O requests to complete.
CMEMTHREAD	0x207	This waittype indicates that the SPID is waiting for access to a thread-safe memory object. The serialization makes sure that while the users are allocating or freeing the memory from the memory object, any other SPIDs that are trying to perform the same task have to wait, and the CMEMTHREAD waittype is set when the SPIDs are waiting. You may 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.
CXPACKET	0x208	These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
PAGESUPP	0x209	This waittype tracks the wait time that is incurred because of the required serialization in distributing rows to multiple callers in a parallel scan.
SHUTDOWN	0x20A	This waittype indicates that a SHUTDOWN command has been issued by the SPID, and the SPID is waiting for active queries to complete.
WAITFOR	0x20B	This waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement.
CURSOR	0x20C	This waittype indicates that the SPID is participating in the thread synchronization while it uses asynchronous cursors. The sp_configure ‘cursorthreshold’ configuration setting may determine when a cursor is created asynchronously.
EXECSYNC	0x20D	General sync during execution
GROUP	0x400	All types with 0x400 bit set are latch types
LATCH_NL	0x400	NULL latch
LATCH_KP	0x401	Keep latch
LATCH_SH	0x402	Shared latch
LATCH_UP	0x403	Update latch
LATCH_EX	0x404	Exclusive latch
LATCH_DT	0x405	Destroy latch
PAGELATCH_NL	0x410	NULL buffer page latch
PAGELATCH_KP	0x411	Keep buffer page latch
PAGELATCH_SH	0x412	Shared buffer page latch
PAGELATCH_UP	0x413	Update buffer page latch
PAGELATCH_EX	0x414	Exclusive buffer page latch
PAGELATCH_DT	0x415	Destroy buffer page latch
PAGEIOLATCH_NL	0x420	NULL buffer page I/O latch
PAGEIOLATCH_KP	0x421	Keep buffer page I/O latch
PAGEIOLATCH_SH	0x422	Shared buffer page I/O latch
PAGEIOLATCH_UP	0x423	Update buffer page I/O latch
PAGEIOLATCH_EX	0x424	Exclusive buffer page I/O latch
PAGEIOLATCH_DT	0x425	Destroy buffer page I/O latch
TRAN_MARK_NL	0x430	NULL transaction latch
TRAN_MARK_KP	0x431	Keep transaction latch
TRAN_MARK_SH	0x432	Shared transaction latch
TRAN_MARK_UP	0x433	Update transaction latch
TRAN_MARK_EX	0x434	Exclusive transaction latch
TRAN_MARK_DT	0x435	Destroy transaction latch
NETWORKIO	0x800	This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.