Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Definition of sysprocesses waittype and lastwaittype columns for SQL Server 7.0


View products that this article applies to.

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.

↑ Back to the top


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.

LastwaittypeWaittypeDescription
LCK_M_SCH_S0x01Schema stability
LCK_M_SCH_M0x02Schema modification
LCK_M_IS0x03Intent-Share
LCK_M_SIU0x04Shared intent to update
LCK_M_IS_S0x05Intent-Share-Share (Key-Range Lock)
LCK_M_IX0x06Intent-Exclusive
LCK_M_SIX0x07Share-Intent-Exclusive
LCK_M_S0x08Share
LCK_M_U0x09Update
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_X0x0EExclusive
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.

LastwaittypeWaittypeDescription
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:

LastwaittypeWaittypeDescription
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.

↑ Back to the top


Keywords: kbinfo, KB244455

↑ Back to the top

Article Info
Article ID : 244455
Revision : 4
Created on : 5/2/2005
Published on : 5/2/2005
Exists online : False
Views : 878