Microsoft KB Archive/822101

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

Article ID: 822101

Article Last Modified on 5/17/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * 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 Edition

-



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 &quot;Other waittypes&quot; 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.

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.

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.

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.

