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

???? ID: 822101 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

Themaster.dbo.sysprocessestable 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 thesys.sysprocessescompatibility view.

Thewaittypecolumn, thelastwaittypecolumn, thewaittimecolumn, and thewaitresource?????master.dbo.sysprocessessystem 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.
????? ?? ???? ???? | ??????????? ???

???? ???????

Themaster.dbo.sysprocessessystem table that is stored in the??????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:waittypefield is a reserved internal binary column. The value in thewaittypefield indicates the type of resource that the connection is waiting on.
  • lastwaittype:lastwaittypefield is a string representation of thewaittype????? ??? Thelastwaittypefield indicates the last waittype or the current waittype of a SPID. If the value of thewaittypecolumn for an SPID is 0x0000, the SPID is not currently waiting on any resource. ?? ?????? ???,lastwaittypecolumn indicates the last waittype that the SPID has experienced. However, if the value of thewaittypecolumn for an SPID is non-zero, the values in thelastwaittypecolumn and thewaittypecolumn for the SPID are equal. These values indicate the current wait state for the SPID.

    ???:The UMSTHREAD waittype is an exception to this rule. See the description of UMSTHREAD in the "Other waittypes" section for additional details.
  • waittime:waittimecolumn 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 themaster.dbo.sysprocesses?????? ?????

LOCK waittypes

The following table lists the LOCK waittypes in themaster.dbo.sysprocessessystem table in SQL Server 2000 and SQL Server 2005.
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
LastwaittypeWaittype?????:
LCK_M_SCH_S0x01Schema stability
LCK_M_SCH_M0x02Schema modification
LCK_M_S0x03????
LCK_M_U0x04??????
LCK_M_X0x05?????
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 thewaittimecolumn that is logged in themaster.dbo.sysprocessessystem table is very high, you must troubleshoot the blocking problems for your instance of SQL Server.

???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
224453
(http://support.microsoft.com/kb/224453/ )
SQL Server 7.0 ?? 2000 ????????? ?????? ?? ??? ???? ?? ?? ?????

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 themaster.dbo.sysprocessestable 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
    (http://support.microsoft.com/kb/328551/ )
    FIX: tempdb ??????? ?? ??? Concurrency ?????????????
  • 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 themaster.dbo.sysprocessessystem table in SQL Server 2000 and SQL Server 2005.
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
LastwaittypeWaittype?????:
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


????? ?????? ??? ????? ?? ????? ?? ??????? ???? ???? SQL Server 2000 SP4, ???? ??????? ???-Buf latches, Latch_XX ?? ??? waitresource ????? latches, ????????? ??? SQL Server 2000 SP4, ???? ?? ???? ??? ???? ?? ?? ???-buf latches ???? ???? invasive ?????, ???? ??? userdump held ?? ??? ?? ?? ????????? ???? ?? ??? ??? ????? ???? ??? Waittype ?? waitresource waittime ?? ????? ???? ???? ?? ???? ?????? Waittime ??? ????????? ???? ?? ????????? ????? ????? ????????? ?? ???? ??? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
Waitresource?????:?????? ??????
FCB FGCB_ALLOC?? Latch_XX latches ??? held ??????? ?? ???? ????? ?? ?????? ??? ?? ?? ?? ?? ????-???? ?? ?????????? ???? ??? ????? ????????, ???? growing ?? ???? ????? ?? shrinking, ?? ??? ????? ?? ???? ???? ?? ??? ?? ??? ?? ???? ????? ?? ??????? ?? ????? latches held ?? ??? ????????????? ???? ?? ???? ??? ?? ??????? shrinking ?? growing ???? SQL Server 2000 SP4 ?? ??? ??????? ?? ??? ??, Autogrow ?????? ???? ??? errorlog ??? ??? ??? 30 ????? ?? ???? ???? ??? Ideally, Autogrow ?????? ?? ????? ?? ??? ??? ????? ???? ???? ?????, ?? ?? ????? growing ?? ?????? penalty ?? ???? ?? ??? ????? prudent ?????? ?? ????? ???? ?????? ?? ??? ??????? ???? ?? ??? ?? autogrow 10 ??????? ??????? ??? ??? ???? ???? ?? ??????? ???? ?? ??????? ??? Autoshrink ?? ??? ??????? ?? ?? filegroup ??? ?? ???? ?? ?? ???? ???? allocations ?? ??? ?? ??????? ?? ???? ??????? ?? filegroup ??? ???? ???? ????
????? TRACE_CONTROLLER TRACE_IO_SYNC?? Latch_XX latches profiler ????? ???????? ??? ????????????? ?????????? ?? ??? held ?? ??? ???? Sychronization ???????? ????? ??????? ????, ?????, ????? ?????? ??? ????, ?? ???? ?? ????? ?????????? ???? ?? ??? ??????? ?? ?? ??? profiler traces ?? ????? ????? ?? ?? ??? ??? ?? ?? ???? profiler ????? ?? ??? ?? ???? ?? ?????? ????? ?? ??? profiler ????? ??????? ???? ??? ??, ????? ????????? ????? ??? ??? ??? ?? ??? ??? ??, ?? ????? ?? ???? ????? ?????? SQL ?????? ???????? ??? ?????? traces ????? ?? ????
SELECT * FROM 
::fn_trace_getinfo(NULL)
PARALLEL_PAGE_SUPPLIER?? Latch_XX latches ???? ?? ??? ?????? ?????? ?? ??? ??????? ?? ?????????? ???? ?? ??? ????? ???? ???? ??? ?? latch ????? ???? ?? ?? ?????? ?????? ??? ?? ??? ?? ?? ??????? ???? ?? ?????????? ???? ??? waittime ????? ?? ?? ???? ??? ??, ?? ?????? poorly ?? ??? ???
IDESIDES latch waitresource ????? ???? ???? ?? ?? PFS ????? ????? ????? ?????? ?? ??? ????? ???? ?? ?? ?? PFS ????? allocations ?? ??????? ?? deallocations ?? ??????????? ???? ?? ??? ?????? ??? ??? ????? ?????? ???? ?? ??? ???? ?????? ???????? ??? ?? ?? latch ?? ????? ??? ???????????? ???? ?? ???? ??? question ???????? ?? ??? ????? ??????????? ???

???? waittypes

????? ?????? ???? waittypes ??? ????? ?? ???? ?? ?? ???? ???? ???? ??master.dbo.sysprocesses?????? ???? SQL Server 2000 ?? SQL Server 2005 ??? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
LastwaittypeWaittype?????:
?????0x20?? waittype ????? ???? ?? ?? SPID ?? ????????? ??? ?? ??? ????????? ?? ??? ?? ?? ????????? ??????? ?? lazywrites, checkpoints, ?? ?????-???? profiler ????? ??????? ?? ??????? ???? ?? ??? ?? ??????? ?????? ??? ???
IO_COMPLETION0x21?? waittype ????? ???? ?? ?? SPID i/O ???????? ?? ???? ???? ?? ??? ????????? ?? ??? ??? ?? ?? ????? ?? waittype ??? ?? SPID ?? ???sysprocesses?????? ????, ???? ?????? ????? ????? bottlenecks SPID ???? ?? ??? ???? ?????? ??????? ?? ???????? ???? ?? ??? ???????? ?????? ????????, profiler ?????, fn_virtualfilestats ?????? ???? valued ?????? ?? SHOWPLAN ?????? ?? ????? ??? ?? ???????? i/O ????????? ?? ?????? ?? ????? ???????? ?? i/O ?? ?????? ?????? ?? waittype ??? ???? ???? ?? ?? ???? ??? ?? ??????????? ?? ????? ???? ??? i/O ?? ??, ??? ?????? ??????? ?? ?????, ?? ?????? ???? ?? ??? ??????
ASYNC_IO_COMPLETION0x22?? waittype ????? ???? ?? ?? SPID ????? ???? ?? ??? ?????????? i/O ?????? ?? ??? ????????? ?? ??? ??? ????? ???? IO_COMPLETION waittype ?? ??? ?? waittype ?? ?? ?? i/O bottleneck. ?? ??? ???? ??? ?? waittype SPID ?? ??? ???? ???? I/O-????? ???????????, ???? ?? ?????, ??????? ?????, ???????? ???????, ?? ??????? autogrow ?? ?????? ?? waittype ????? bottlenecks ?? ?? ????? ?? ???? ???
RESOURCE_SEMAPHORE0x40?? waittype ????? ???? ?? ?? SPID ???? ?????? ?? ????????? ?? ??? ??? ????, SPID ????????: ????????? ????? ?? ?????? ???????? ?? ????? ?????? ???????? ?? ??? ?????? ?? ??????? ???? ?? ???? ?? waittype ?? ????? ?? ???? ?? ?? ?????? ???? ???? ??? ??? ??? ??? ??? ????? ???
DTC0x41?? waittype ????? ???? ?? ?? SPID Microsoft ?????? ?????? Coordinator ?? ????????? ?? ??? ?? (MS DTC) ?????
OLEDB0x42?? waittype ????? ???? ?? ?? ??? SPID ??? ?????? ?? ??? ???? ?? ??? ??? OLE DB ??????? ????? ?? ?? ?????? ???? ?? ???? ???? ?? ??? ?????? ?? ??? ????????? ?? ??? ??? ?? waittype ?? SPID ?????? ???? ?? ???? ???? ?? ??? ?????? ????????? ??? ?? ???? ??? ?? ????? ?????? ?? ??? ????????? ?? ??? ?? ?? ????? ?? ?? ???? ??? SPID ?????? ???? ?? ???? ???? ?? ??? ???? ?????? ??? ???????? ???? ???? ?? ????? ??? ?????? ?? ??? ??????????? ?? ???? ?? ???? ?? ???? ???

OLEDB waittype several ????? ????????? ??????, ?????? ????? ???? ?? ??? ????? ???? ???? ??: ????? ????????, ?????-??? ??? ????????, ???? ?????? ??? ???????? ???? ????, ???????-???? Profiler traces, ?? ??? sysprocesses ??????, ??? ???? ?? DBCC CHECKDB ??????? ???????? materialization ?? ???? ?? ???
RESOURCE_QUEUE0x44?? SQL ????? ??? ??????? ?? ????????? ?? ??? ?? ??????? ? ????????? ? ?????? ???
ASYNC_DISKPOOL_LOCK0x45?? ?? waittype ???? ?????, ??????? ????, ?? ???? ??????? ????? ?? ?????? ???? ???? I/O-????? ??????????? ?? ????? ????? ?? ???? ???
UMSTHREAD0x46?? waittype ????? ???? ?? ?? ?? ??? ?? ??????? ????????? ?? ??????? ???? ??? ?? ????? ?? ??? ?? ?????? ???? ?? ??? ?????? ????? ????? ???? ??? ??? ?? consistently ????? 0x0046 waittypes ?????? SPID ?? ???, ?? ?????????? bottleneck elsewhere ??? ?? ?????? ?? ?? ??? ?????? ????? ????? ?? ????? ?? ??? ??? ?? ???waittime????? UMSTHREAD waittype ?? ??? 0 ????? ?? ??lastwaittype????? ?? ???? ?? erroneously ?????? UMSTHREAD ?? ????? ?? ?? ????? waittype ?? ???."
WRITELOG0x81?? waittype ????? ???? ?? ?? SPID ????? ???? ?? ??? ????????? ??? i/O ?????? ?? ??? ????????? ?? ??? ??? 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. Thesp_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.
WAITFOR0x20B?? waittype ????? ???? ?? ?? SPID ?? ???? ??? WAITFOR ????? Transact-SQL sleeping ?? ????
NETWORKIO0x800?? waittype ????? ???? ?? ?? SPID SPID ??????? ????????? ?? ??? ?? ???? ?????? ?? ??? ???? ?? ???? ???? ?? fetch ???? ?? ??? ??????? ????????? ?? ??? ????????? ?? ??? ???
????? ?? ???? ???? | ??????????? ???

??????

???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
244455
(http://support.microsoft.com/kb/244455/ )
SQL Server 7.0 ?? ??? sysprocesses waittype ?? lastwaittype ????? ?? ????????
?? ???? ??? ???????? ??????? ?? ???master.dbo.sysprocessesSQL Server 2000 ??? ?????? ???? ?? ????? Microsoft ??? ???? ?? ????:
(SQL.80) http://msdn2.Microsoft.com/en-us/library/aa260456 .aspx
(http://msdn2.microsoft.com/en-us/library/aa260456(SQL.80).aspx)
??? SQL Server 2000 latches ?? ???? ??? ???? ??????? ?? ??? ????? Microsoft ??? ???? ?? ????:
(SQL.80) http://msdn2.Microsoft.com/en-us/library/aa224727 .aspx
(http://msdn2.microsoft.com/en-us/library/aa224727(SQL.80).aspx)
????? ?? ???? ???? | ??????????? ???

???

???? ID: 822101 - ????? ???????: 05 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • 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
??????: 
kbsystemdata kbfield kbdatabase kbinfo kbmt KB822101 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:822101
(http://support.microsoft.com/kb/822101/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????