Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005 (822101)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup

SUMMARY

The master.dbo.sysprocesses table in Microsoft SQL Server 2000 and SQL Server 2005 is a system table that contains information about the active server process IDs (SPIDs) that are running on SQL Server.

If you are using SQL Server 2005, you can also access this table by using the sys.sysprocesses compatibility view.

The waittype column, the lastwaittype column, the waittime column, and the waitresource column in the master.dbo.sysprocesses system table provide information about the resources that the processes are waiting on.

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

MORE INFORMATION

The master.dbo.sysprocesses system table that is stored in the master database of your instance of SQL Server contains the following columns that help the system administrator monitor the processes for resource contention scenarios and blocking problems:
  • waittype: The waittype field is a reserved internal binary column. The value in the waittype field indicates the type of resource that the connection is waiting on.
  • lastwaittype: The lastwaittype field is a string representation of the waittype field. The lastwaittype field indicates the last waittype or the current waittype of a SPID. If the value of the waittype column for an SPID is 0x0000, the SPID is not currently waiting on any resource. In this case, the lastwaittype column indicates the last waittype that the SPID has experienced. However, if the value of the waittype column for an SPID is non-zero, the values in the lastwaittype column and the waittype column for the SPID are equal. These values indicate the current wait state for the SPID.

    Note The UMSTHREAD waittype is an exception to this rule. See the description of UMSTHREAD in the "Other waittypes" section for additional details.
  • waittime: The waittime column provides the number of milliseconds that the SPID has been waiting with the current waittype.
  • waitresource: The waitresource column provides more detailed information about the specific resource that the SPID is waiting on.
The later sections in this article describe some of the waittypes that are supported by SQL Server and that are frequently logged in the master.dbo.sysprocesses system table.

LOCK waittypes

The following table lists the LOCK waittypes in the master.dbo.sysprocesses system table in SQL Server 2000 and SQL Server 2005.
LastwaittypeWaittypeDescription
LCK_M_SCH_S0x01Schema stability
LCK_M_SCH_M0x02Schema modification
LCK_M_S0x03Share
LCK_M_U0x04Update
LCK_M_X0x05Exclusive
LCK_M_IS0x06Intent-Share
LCK_M_IU0x07Intent-Update
LCK_M_IX0x08Intent-Exclusive
LCK_M_SIU0x09Shared intent to update
LCK_M_SIX0x0aShare-Intent-Exclusive
LCK_M_UIX0x0bUpdate-Intent-Exclusive
LCK_M_BU0x0cBulk Update
LCK_M_RS_S0x0dRange-share-share
LCK_M_RS_U0x0eRange-share-Update
LCK_M_RI_NL0x0FRange-Insert-NULL
LCK_M_RI_S0x10Range-Insert-Shared
LCK_M_RI_U0x11Range-Insert-Update
LCK_M_RI_X0x12Range-Insert-Exclusive
LCK_M_RX_S0x13Range-exclusive-Shared
LCK_M_RX_U0x14Range-exclusive-update
LCK_M_RX_X0x15Range-exclusive-exclusive
For more information about the LOCK types that are supported in SQL Server 2000 and SQL Server 2005, see the following topics in SQL Server Books Online:
  • Understanding locking in SQL Server
  • Lock compatibility
Additionally, if an SPID is waiting on the LOCK waittypes and the corresponding value in the waittime column that is logged in the master.dbo.sysprocesses system table is very high, you must troubleshoot the blocking problems for your instance of SQL Server.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

224453 Understanding and resolving SQL Server 7.0 or 2000 blocking problems

LATCH waittypes

A latch is a short-term lightweight synchronization object. The following list describes the different types of latches:
  • Non-buffer (Non-BUF) latch: The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
  • Buffer (BUF) latch: The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction. These are indicated in the master.dbo.sysprocesses table by the PAGELATCH waittypes.

    For more information about one of the possible causes of BUF latch contention, click the following article number to view the article in the Microsoft Knowledge Base:

    328551 FIX: Concurrency enhancements for the tempdb database

  • IO latch: The IO latches are a subset of BUF latches that are used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I/O subsystem issues.
The following table lists the different latch waittypes that you may notice in the master.dbo.sysprocesses system table in SQL Server 2000 and SQL Server 2005.
LastwaittypeWaittypeDescription
LATCH_NL0x400Null latch
LATCH_KP0x401Keep latch
LATCH_SH0x402Shared latch
LATCH_UP0x403Update latch
LATCH_EX0x404Exclusive latch
LATCH_DT0x405Destroy latch
PAGELATCH_NL0x410Null buffer page latch
PAGELATCH_KP0x411Keep buffer page latch
PAGELATCH_SH0x412Shared buffer page latch
PAGELATCH_UP0x413Update buffer page latch
PAGELATCH_EX0x414Exclusive buffer page latch
PAGELATCH_DT0x415Destroy buffer page latch
PAGEIOLATCH_NL0x420Null buffer page I/O latch
PAGEIOLATCH_KP0x421Keep buffer page I/O latch
PAGEIOLATCH_SH0x422Shared buffer page I/O latch
PAGEIOLATCH_UP0x423Update buffer page I/O latch
PAGEIOLATCH_EX0x424Exclusive buffer page I/O latch
PAGEIOLATCH_DT0x425Destroy buffer page I/O latch


Starting with SQL Server 2000 SP4, the waitresource column for the most common Non-Buf latches, the Latch_XX latches, is populated by using the values in the following table. Before SQL Server 2000 SP4, there was no way to determine the reason that the Non-buf latches were being held without more invasive diagnostics, such as a userdump. The waittype or the waitresource should be viewed by using the waittime. The waittime specifies in milliseconds how long the thread has been waiting.
WaitresourceDescriptionTroubleshooting
FCB FGCB_ALLOCThese Latch_XX latches are held when pages are allocated from a file, whether it is a uniform or mixed extent. The latches are also held during allocation operations, such as growing and shrinking a file, renaming or dropping a file, or adding or removing a file.Determine whether any files are shrinking or growing. Starting with SQL Server 2000 SP4, the Autogrow option will be logged in the errorlog if it takes more than 30 seconds. Ideally, the Autogrow option should be used as a backup, and you should use prudent capacity planning to avoid the runtime penalty of growing the file. You can also change the 10 percent default autogrow value to a fixed size. We do not recommend that you use the Autoshrink in production. The files in this filegroup may also be issuing many requests for allocations. You can add more files in that filegroup.
TRACE TRACE_CONTROLLER TRACE_IO_SYNC These Latch_XX latches are held for synchronization purposes in profiler trace operations. Sychronization operations include starting, stopping, setting the trace status, and synchronizing the readers and writers. Identify any profiler traces that are running. Try to avoid running more than one profiler trace at the same time. When a profiler trace is started, the trace is logged in the application event log. Additionally, you can use the following query in SQL Query Analyzer to view active traces. 
SELECT * FROM 
::fn_trace_getinfo(NULL)
PARALLEL_PAGE_SUPPLIERThese Latch_XX latches are used for synchronizing the retrieval of pages for a parallel query. This latch indicates that the query is running in parallel and could be typical.Tune the query if the waittime column is a large value, and the query is performing poorly.
IDESThe IDES latch waitresource is used when the PFS page is scanned to find free space and when the PFS page is updated to reflect allocations and deallocations of pages. This latch is also used when single pages are allocated to an existing object.Determine whether you have a clustered index on the object in question.

Other waittypes

The following table lists the other waittypes that you may notice in the master.dbo.sysprocesses system table in SQL Server 2000 and SQL Server 2005.
LastwaittypeWaittypeDescription
SLEEP0x20This waittype indicates that the SPID is waiting for a specified time and is a common state for the background threads that process the lazywrites, the checkpoints, or the server-side profiler trace events.
IO_COMPLETION0x21This waittype indicates that the SPID is waiting for the I/O requests to complete. When you notice this waittype for an SPID in the sysprocesses system table, you must identify the disk bottlenecks by using the performance monitor counters, profiler trace, the fn_virtualfilestats system table-valued function, and the SHOWPLAN option to analyze the query plans that correspond to the SPID. You can reduce this waittype by adding additional I/O bandwidth or balancing I/O across other drives. You can also reduce I/O by using indexing, look for bad query plans, and look for memory pressure.
ASYNC_IO_COMPLETION0x22This waittype indicates that the SPID is waiting for the asynchronous I/O requests to complete. Like the IO_COMPLETION waittype, this waittype also indicates an I/O bottleneck. You may see this waittype for the SPIDs during the long-running I/O-bound operations, such as BACKUP, CREATE DATABASE, ALTER DATABASE, or the database autogrow. This waittype may also indicate disk bottlenecks.
RESOURCE_SEMAPHORE0x40This waittype indicates that the SPID is waiting on a resource. Here, the SPIDs generally wait to acquire the memory for the sorting or the hashing operation during the query execution. This waittype may also indicate that memory pressure exists in the visible part of the buffer pool.
DTC0x41This waittype indicates that the SPID is waiting on the Microsoft Distributed Transaction Coordinator (MS DTC) service.
OLEDB0x42This waittype indicates that an SPID has made a function call to an OLE DB provider and is waiting for the function to return the required data. This waittype may also indicate that the SPID is waiting for remote procedure calls or linked server queries to return the required data. The SPID may also be waiting for BULK INSERT commands or full-search queries to return the required data.

The OLEDB waittype is used to indicate several different wait states, including: linked server queries, full-text search queries, BULK INSERT commands, client-side Profiler traces, materialization of virtual tables like the sysprocesses table, log reader, and DBCC CHECKDB.
RESOURCE_QUEUE0x44This is an ordinary "idle" state for background threads in SQL Server.
ASYNC_DISKPOOL_LOCK0x45You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file.
UMSTHREAD0x46This waittype indicates that a batch has been received from a client application but that there are no worker threads that are available to service the request. If you consistently see 0x0046 waittypes for multiple SPIDs, there is a significant bottleneck elsewhere in the system that is using all the available worker threads. Note that the waittime column is always 0 for the UMSTHREAD waittype, and the lastwaittype column may erroneously show the name of a different waittype instead of UMSTHREAD."
WRITELOG0x81This waittype indicates that the SPID is waiting for a transaction log I/O request to complete. This waittype may also indicate a possible disk bottleneck.
PSS_CHILD
EXCHANGE
CXPACKET		0x101
0x200
0x208 		These waittypes are all involved in parallel query execution. These waittypes indicate that the SPID is waiting on a parallel process to complete or start.
PAGESUPP0x209This waittype tracks the wait time that is incurred because of the required serialization in distributing rows to multiple callers in a parallel scan.
CURSOR0x20CThis waittype indicates that the SPID is participating in the thread synchronization while it uses asynchronous cursors. The sp_configure 'cursorthreshold' configuration setting may determine when a cursor is created asynchronously.
DBTABLE0x202This waittype indicates that a thread is waiting to perform a checkpoint and another thread is already checkpointing the database.
EC0x203This waittype indicates that the SPID is waiting for access to execution context.
TEMPOBJ0x204This waittype indicates that the SPID is waiting to drop a temporary object that is still being used.
XACTLOCKINFO0x205This waittype indicates that the SPID is waiting to perform maintenance on its lock list.
LOGMGR0x206This waittype is used when the SPID tries to shut down a database and waits for the pending transaction log I/O requests to complete.
CMEMTHREAD0x207This waittype indicates that the SPID is waiting for access to a thread-safe memory object. The serialization makes sure that while the users are allocating or freeing the memory from the memory object, any other SPIDs that are trying to perform the same task have to wait, and the CMEMTHREAD waittype is set when the SPIDs are waiting.

You may notice this waittype in many scenarios. However, this waittype is most frequently logged when the ad hoc query plans are being quickly inserted into a procedure cache from many different connections to the instance of SQL Server. You can address this bottleneck by limiting the data that must be inserted or removed from the procedure cache, such as explicitly parameterizing the queries so that the queries can be reused or using stored procedures where appropriate.
SHUTDOWN0x20AThis waittype indicates that a SHUTDOWN command has been issued by the SPID, and the SPID is waiting for active queries to complete.
WAITFOR0x20BThis waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement.
NETWORKIO0x800This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.

REFERENCES

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

244455 Definition of sysprocesses waittype and lastwaittype fields for SQL Server 7.0

For additional information about the master.dbo.sysprocesses system table in SQL Server 2000, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-p_3kmr.asp

For more information about the latches in SQL Server 2000, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0odj.asp

Modification Type:MajorLast Reviewed:12/14/2005
Keywords:kbSystemData kbfield kbDatabase kbinfo KB822101 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.