Definition of sysprocesses waittype and lastwaittype columns for SQL Server 7.0 (244455)

SUMMARY

The sysprocesses table in SQL Server is a virtual table containing information about active server process IDs (SPIDs). The lastwaittype field is new in SQL Server 7.0 and is a string representation of the waittype field (which is a reserved internal binary column). It indicates the last or current waittype of a SPID. If the waittype is 0x0000, the SPID is not currently waiting on anything and the lastwaittype value indicates the last waittype the SPID experienced. If the waittype is non-zero, the lastwaittype and waittype will be equivalent and indicate the current waitstate for the SPID.

This article lists the possible lastwaittype values, their associated waittype values, and a brief description of their meaning.

MORE INFORMATION

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.