Tuesday, October 6, 2009

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 NameNumeric Wait Type Description
MISCELLANEOUS0x00Collection bucket for all unknown wait types, should be zero. In any case it does not represent any meaningful data.
LCK_M_SCH_S0x01Schema stability lock
LCK_M_SCH_M0x02Schema modification lock
LCK_M_S0x03Share lock
LCK_M_U0x04 Update lock
LCK_M_X0x05Exclusive lock
LCK_M_IS0x06Intent-Share lock
LCK_M_IU0x07Intent-Update lock
LCK_M_IX0x08Intent-Exclusive lock
LCK_M_SIU0x09Shared intent to update lock
LCK_M_SIX0x0AShare-Intent-Exclusive lock
LCK_M_UIX0x0BUpdate-Intent-Exclusive lock
LCK_M_BU0x0CBulk Update lock
LCK_M_RS_S0x0DRange-share-share lock
LCK_M_RS_U0x0ERange-share-Update lock
LCK_M_RIn_NL0x0FRange-Insert-NULL lock
LCK_M_RIn_S0x10Range-Insert-Shared lock
LCK_M_RIn_U0x11Range-Insert-Update lock
LCK_M_RIn_X0x12Range-Insert-Exclusive lock
LCK_M_RX_S0x13Range-exclusive-Shared lock
LCK_M_RX_U0x14Range-exclusive-update lock
LCK_M_RX_X0x15Range-exclusive-exclusive lock
GROUP0x20All types with 0x20 are now used for I/O COMPLETION
SLEEP0x20This 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_COMPLETION0x21This 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_COMPLETION0x22This 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
GROUP0x40All type with 0x40 do not below to a specific group
RESOURCE_SEMAPHORE0x40This 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

DTC0x41This waittype indicates that the SPID is waiting on the Microsoft Distributed Transaction Coordinator (MS DTC) service.
OLEDB0x42This 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.
RESOURCE_QUEUE0x44This is an ordinary “idle” state for background threads in SQL Server.
ASYNC_DISKPOOL_LOCK0x45You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file.
UMS_THREAD0x46This 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_STAT0x47// 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. //
GROUP0x80All types with 0x80 bit set relate to DBTABLE type of waits
WRITELOG0x81This 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 )
LOGBUFFER0x82Waiting on a free buffer
GROUP0x100All types with 0x100 bit set is waiting for a pss
PSS_CHILD0x101These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
GROUP0x200All types with 0x200 bit set are using upwait ()
EXCHANGE0x200These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
DBTABLE0x202This waittype indicates that a thread is waiting to perform a checkpoint and another thread is already checkpointing the database.
EC0x203This waittype indicates that the SPID is waiting for access to execution context.
TEMPOBJ0x204This waittype indicates that the SPID is waiting to drop a temporary object that is still being used.
XACTLOCKINFO0x205This waittype indicates that the SPID is waiting to perform maintenance on its lock list.
LOGMGR0x206This waittype is used when the SPID tries to shut down a database and waits for the pending transaction log I/O requests to complete.
CMEMTHREAD0x207This 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.
CXPACKET0x208These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
PAGESUPP0x209This waittype tracks the wait time that is incurred because of the required serialization in distributing rows to multiple callers in a parallel scan.
SHUTDOWN0x20AThis waittype indicates that a SHUTDOWN command has been issued by the SPID, and the SPID is waiting for active queries to complete.
WAITFOR0x20BThis waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement.
CURSOR0x20CThis 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.
EXECSYNC0x20DGeneral sync during execution
GROUP0x400All types with 0x400 bit set are latch types
LATCH_NL0x400NULL latch
LATCH_KP0x401Keep latch
LATCH_SH0x402Shared latch
LATCH_UP0x403Update latch
LATCH_EX0x404Exclusive latch
LATCH_DT0x405Destroy latch
PAGELATCH_NL0x410NULL buffer page latch
PAGELATCH_KP0x411Keep buffer page latch
PAGELATCH_SH0x412Shared buffer page latch
PAGELATCH_UP0x413Update buffer page latch
PAGELATCH_EX0x414Exclusive buffer page latch
PAGELATCH_DT0x415Destroy buffer page latch
PAGEIOLATCH_NL0x420NULL buffer page I/O latch
PAGEIOLATCH_KP0x421Keep buffer page I/O latch
PAGEIOLATCH_SH0x422Shared buffer page I/O latch
PAGEIOLATCH_UP0x423Update buffer page I/O latch
PAGEIOLATCH_EX0x424Exclusive buffer page I/O latch
PAGEIOLATCH_DT0x425Destroy buffer page I/O latch
TRAN_MARK_NL0x430NULL transaction latch
TRAN_MARK_KP0x431Keep transaction latch
TRAN_MARK_SH0x432Shared transaction latch
TRAN_MARK_UP0x433Update transaction latch
TRAN_MARK_EX0x434Exclusive transaction latch
TRAN_MARK_DT0x435Destroy transaction latch
NETWORKIO0x800This 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.

No comments:

Post a Comment