The following waittypes indicate waiting on the lock specified in the
Description column. The
waitresource column will show the specific resource the SPID is attempting to lock.
| Lastwaittype | Waittype | Description |
|---|
| LCK_M_SCH_S | 0x01 | Schema stability |
| LCK_M_SCH_M | 0x02 | Schema modification |
| LCK_M_IS | 0x03 | Intent-Share |
| LCK_M_SIU | 0x04 | Shared intent to update |
| LCK_M_IS_S | 0x05 | Intent-Share-Share (Key-Range Lock) |
| LCK_M_IX | 0x06 | Intent-Exclusive |
| LCK_M_SIX | 0x07 | Share-Intent-Exclusive |
| LCK_M_S | 0x08 | Share |
| LCK_M_U | 0x09 | Update |
| LCK_M_II_NL | 0x0A | Intent-Insert-NULL (Key-Range Lock) |
| LCK_M_II_X | 0x0B | Intent-Insert-Exclusive (Key-Range Lock) |
| LCK_M_IU | 0x0C | Intent-Update lock |
| LCK_M_IS_U | 0x0D | Intent-Share Update (Key-Range Lock) |
| LCK_M_X | 0x0E | Exclusive |
| LCK_M_BU | 0x0F | Bulk Update |
The following waittypes indicate waiting on a page latch that is specified in the
Description column. The
waitresource column will show the resource which the process is attempting to acquire a latch on. The lastwaittype string will indicate the latch mode the process is waiting on. For more information on page latches, see the "Latching" topic in SQL Server 7.0 Books Online.
| Lastwaittype | Waittype | Description |
|---|
| PWAIT_LATCH_NL | 0x400 | Null latch |
| PWAIT_LATCH_EX | 0x401 | Exclusive latch |
| PWAIT_LATCH_SH | 0x402 | Shared latch |
| PWAIT_LATCH_UP | 0x403 | Update latch |
| PWAIT_PAGELATCH_NL | 0x410 | Null page latch |
| WAIT_PAGELATCH_EX | 0x411 | Exclusive page latch |
| WAIT_PAGELATCH_SH | 0x412 | Shared page latch |
| PWAIT_PAGELATCH_UP | 0x413 | Update page latch |
| PWAIT_PAGEIOLATCH_NL | 0x420 | Null input/output (I/O) page latch |
| PWAIT_PAGEIOLATCH_EX | 0x421 | Exclusive I/O page latch |
| PWAIT_PAGEIOLATCH_SH | 0x422 | Shared I/O page latch |
| PWAIT_PAGEIOLATCH_UP | 0x423 | Update I/O page latch |
The following events indicate that the execution of the user connection is suspended until a particular event happens:
| Lastwaittype | Waittype | Description |
|---|
| PWAIT_RESOURCE_SEMAPHORE | 0x40 | Waiting to acquire a resource semaphore. Used for synchronization. |
| PWAIT_DTC | 0x41 | Waiting on Distributed Transaction Coordinator (DTC). |
| PWAIT_OLEDB | 0x42 | Waiting on an OLE DB provider. |
| PWAIT_WRITELOG | 0x81 | Waiting for log records for a transaction to be flushed to disk. |
| PWAIT_PSS_CHILD | 0x101 | Waiting on a child thread in asynchronous cursor operations. |
| PWAIT_EXCHANGE | 0x200 | Exchange synchronization up for parallel query threads. |
| PWAIT_XCB | 0x201 | Acquiring access to a transaction control block.
Transaction control blocks (XCBs) are usually private to a session, but can be shared between sessions when using the bound session feature or having multiple sessions enlist in the same DTC transaction. Only a single session can have access to the XCB at a time. This waittype likely indicates one session waiting for the XCB while the other session which is holding the XCB resource is waiting on a separate resource. |
| PWAIT_DBTABLE | 0x202 | Only used by Checkpoint process. |
| PWAIT_EC | 0x203 | Killing a connection subthread or Execution Context. |
| PWAIT_TEMPOBJ | 0x204 | Dropping a Temporary Object. |
| PWAIT_XACTLOCKINFO | 0x205 | Waiting on Bulk Operation when releasing\escalating\transferring locks. |
| PWAIT_LOGMGR | 0x206 | Waiting on log writer. |
| PWAIT_CMEMTHREAD | 0x207 | Waiting on access to memory object. |
| PWAIT_CXPACKET | 0x208 | Waiting on packet synchronize up for exchange operator (parallel query). |
| PWAIT_PAGESUPP | 0x209 | Release Spinlock in parallel query thread. |
| PWAIT_SHUTDOWN | 0x20A | Wait for SPID to finish completion before shutdown. |
| PWAIT_WAITFOR | 0x20B | Wait initiated by a WAITFOR command. |
| PWAIT_CURSOR | 0x20C | Waiting for thread synchronization with asynchronous cursors. |