INF: Definition of Sysprocesses Waittype and Lastwaittype Fields for SQL Server 7.0

ID: Q244455

The information in this article applies to:

Microsoft SQL Server version 7.0

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

PWAIT_LATCH_NL 0x400

PWAIT_LATCH_EX 0x401

PWAIT_LATCH_SH 0x402

PWAIT_LATCH_UP 0x403

PWAIT_PAGELATCH_NL 0x410

WAIT_PAGELATCH_EX 0x411

WAIT_PAGELATCH_SH 0x412

PWAIT_PAGELATCH_UP 0x413

PWAIT_PAGEIOLATCH_NL 0x420

PWAIT_PAGEIOLATCH_EX 0x421

PWAIT_PAGEIOLATCH_SH 0x422

PWAIT_PAGEIOLATCH_UP 0x423

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

Additional query words: synch sync last wait type asynch async

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo