Definition of sysprocesses waittype and lastwaittype columns for SQL Server 7.0

This article was previously published under Q244455
This article has been archived. It is offered "as is" and will no longer be updated.
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.
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.

LCK_M_SCH_S0x01Schema stability
LCK_M_SCH_M0x02Schema modification
LCK_M_SIU0x04Shared intent to update
LCK_M_IS_S0x05Intent-Share-Share (Key-Range Lock)
LCK_M_II_NL0x0AIntent-Insert-NULL (Key-Range Lock)
LCK_M_II_X0x0BIntent-Insert-Exclusive (Key-Range Lock)
LCK_M_IU0x0CIntent-Update lock
LCK_M_IS_U0x0DIntent-Share Update (Key-Range Lock)
LCK_M_BU0x0FBulk 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.

PWAIT_LATCH_NL0x400Null latch
PWAIT_LATCH_EX0x401Exclusive latch
PWAIT_LATCH_SH0x402Shared latch
PWAIT_LATCH_UP0x403Update latch
PWAIT_PAGELATCH_NL0x410Null page latch
WAIT_PAGELATCH_EX0x411Exclusive page latch
WAIT_PAGELATCH_SH0x412Shared page latch
PWAIT_PAGELATCH_UP0x413Update page latch
PWAIT_PAGEIOLATCH_NL0x420Null input/output (I/O) page latch
PWAIT_PAGEIOLATCH_EX0x421Exclusive I/O page latch
PWAIT_PAGEIOLATCH_SH0x422Shared I/O page latch
PWAIT_PAGEIOLATCH_UP0x423Update I/O page latch

The following events indicate that the execution of the user connection is suspended until a particular event happens:

PWAIT_RESOURCE_SEMAPHORE0x40Waiting to acquire a resource semaphore. Used for synchronization.
PWAIT_DTC0x41Waiting on Distributed Transaction Coordinator (DTC).
PWAIT_OLEDB0x42Waiting on an OLE DB provider.
PWAIT_WRITELOG0x81Waiting for log records for a transaction to be flushed to disk.
PWAIT_PSS_CHILD0x101Waiting on a child thread in asynchronous cursor operations.
PWAIT_EXCHANGE0x200Exchange synchronization up for parallel query threads.
PWAIT_XCB0x201Acquiring 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_DBTABLE0x202Only used by Checkpoint process.
PWAIT_EC0x203Killing a connection subthread or Execution Context.
PWAIT_TEMPOBJ0x204Dropping a Temporary Object.
PWAIT_XACTLOCKINFO0x205Waiting on Bulk Operation when releasing\escalating\transferring locks.
PWAIT_LOGMGR0x206Waiting on log writer.
PWAIT_CMEMTHREAD0x207Waiting on access to memory object.
PWAIT_CXPACKET0x208Waiting on packet synchronize up for exchange operator (parallel query).
PWAIT_PAGESUPP0x209Release Spinlock in parallel query thread.
PWAIT_SHUTDOWN0x20AWait for SPID to finish completion before shutdown.
PWAIT_WAITFOR0x20BWait initiated by a WAITFOR command.
PWAIT_CURSOR0x20CWaiting for thread synchronization with asynchronous cursors.
synch sync last wait type asynch async

Article ID: 244455 - Last Review: 12/05/2015 16:21:49 - Revision: 2.2

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbinfo KB244455