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.
Shared intent to update
Intent-Share-Share (Key-Range Lock)
Intent-Insert-NULL (Key-Range Lock)
Intent-Insert-Exclusive (Key-Range Lock)
Intent-Share Update (Key-Range Lock)
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.
Null page latch
Exclusive page latch
Shared page latch
Update page latch
Null input/output (I/O) page latch
Exclusive I/O page latch
Shared I/O page latch
Update I/O page latch
The following events indicate that the execution of the user connection is suspended until a particular event happens:
Waiting to acquire a resource semaphore. Used for synchronization.
Waiting on Distributed Transaction Coordinator (DTC).
Waiting on an OLE DB provider.
Waiting for log records for a transaction to be flushed to disk.
Waiting on a child thread in asynchronous cursor operations.
Exchange synchronization up for parallel query threads.
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.
Only used by Checkpoint process.
Killing a connection subthread or Execution Context.
Dropping a Temporary Object.
Waiting on Bulk Operation when releasing\escalating\transferring locks.
Waiting on log writer.
Waiting on access to memory object.
Waiting on packet synchronize up for exchange operator (parallel query).
Release Spinlock in parallel query thread.
Wait for SPID to finish completion before shutdown.
Wait initiated by a WAITFOR command.
Waiting for thread synchronization with asynchronous cursors.