SQLServer
SQL t서버에서의 WaitType과 LastWaitType 컬럼
손병환
2007. 11. 26. 16:40
Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
| Article ID | : | 822101 |
| Last Review | : | May 17, 2007 |
| Revision | : | 8.1 |
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.
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:
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.
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:
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:
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.
| • | 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. |
LOCK waittypes
The following table lists the LOCK waittypes in the master.dbo.sysprocesses system table in SQL Server 2000 and SQL Server 2005.| Lastwaittype | Waittype | Description |
|---|---|---|
| LCK_M_SCH_S | 0x01 | Schema stability |
| LCK_M_SCH_M | 0x02 | Schema modification |
| LCK_M_S | 0x03 | Share |
| LCK_M_U | 0x04 | Update |
| LCK_M_X | 0x05 | Exclusive |
| LCK_M_IS | 0x06 | Intent-Share |
| LCK_M_IU | 0x07 | Intent-Update |
| LCK_M_IX | 0x08 | Intent-Exclusive |
| LCK_M_SIU | 0x09 | Shared intent to update |
| LCK_M_SIX | 0x0a | Share-Intent-Exclusive |
| LCK_M_UIX | 0x0b | Update-Intent-Exclusive |
| LCK_M_BU | 0x0c | Bulk Update |
| LCK_M_RS_S | 0x0d | Range-share-share |
| LCK_M_RS_U | 0x0e | Range-share-Update |
| LCK_M_RI_NL | 0x0F | Range-Insert-NULL |
| LCK_M_RI_S | 0x10 | Range-Insert-Shared |
| LCK_M_RI_U | 0x11 | Range-Insert-Update |
| LCK_M_RI_X | 0x12 | Range-Insert-Exclusive |
| LCK_M_RX_S | 0x13 | Range-exclusive-Shared |
| LCK_M_RX_U | 0x14 | Range-exclusive-update |
| LCK_M_RX_X | 0x15 | Range-exclusive-exclusive |
| • | Understanding locking in SQL Server |
| • | Lock compatibility |
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
224453 (http://support.microsoft.com/kb/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 (http://support.microsoft.com/kb/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. |
| Lastwaittype | Waittype | Description |
|---|---|---|
| LATCH_NL | 0x400 | Null latch |
| LATCH_KP | 0x401 | Keep latch |
| LATCH_SH | 0x402 | Shared latch |
| LATCH_UP | 0x403 | Update latch |
| LATCH_EX | 0x404 | Exclusive latch |
| LATCH_DT | 0x405 | Destroy latch |
| PAGELATCH_NL | 0x410 | Null buffer page latch |
| PAGELATCH_KP | 0x411 | Keep buffer page latch |
| PAGELATCH_SH | 0x412 | Shared buffer page latch |
| PAGELATCH_UP | 0x413 | Update buffer page latch |
| PAGELATCH_EX | 0x414 | Exclusive buffer page latch |
| PAGELATCH_DT | 0x415 | Destroy buffer page latch |
| PAGEIOLATCH_NL | 0x420 | Null buffer page I/O latch |
| PAGEIOLATCH_KP | 0x421 | Keep buffer page I/O latch |
| PAGEIOLATCH_SH | 0x422 | Shared buffer page I/O latch |
| PAGEIOLATCH_UP | 0x423 | Update buffer page I/O latch |
| PAGEIOLATCH_EX | 0x424 | Exclusive buffer page I/O latch |
| PAGEIOLATCH_DT | 0x425 | Destroy 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.
| Waitresource | Description | Troubleshooting |
| FCB FGCB_ALLOC | These 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 |
| PARALLEL_PAGE_SUPPLIER | These 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. |
| IDES | The 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.| Lastwaittype | Waittype | Description |
|---|---|---|
| SLEEP | 0x20 | This 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_COMPLETION | 0x21 | This 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_COMPLETION | 0x22 | This 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_SEMAPHORE | 0x40 | This 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. |
| DTC | 0x41 | This waittype indicates that the SPID is waiting on the Microsoft Distributed Transaction Coordinator (MS DTC) service. |
| OLEDB | 0x42 | This 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_QUEUE | 0x44 | This is an ordinary “idle” state for background threads in SQL Server. |
| ASYNC_DISKPOOL_LOCK | 0x45 | You may notice this waittype during the long-running I/O-bound operations such as creating, expanding, or dropping a database file. |
| UMSTHREAD | 0x46 | This 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." |
| WRITELOG | 0x81 | This 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. |
| PAGESUPP | 0x209 | This waittype tracks the wait time that is incurred because of the required serialization in distributing rows to multiple callers in a parallel scan. |
| CURSOR | 0x20C | This 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. |
| DBTABLE | 0x202 | This waittype indicates that a thread is waiting to perform a checkpoint and another thread is already checkpointing the database. |
| EC | 0x203 | This waittype indicates that the SPID is waiting for access to execution context. |
| TEMPOBJ | 0x204 | This waittype indicates that the SPID is waiting to drop a temporary object that is still being used. |
| XACTLOCKINFO | 0x205 | This waittype indicates that the SPID is waiting to perform maintenance on its lock list. |
| LOGMGR | 0x206 | This waittype is used when the SPID tries to shut down a database and waits for the pending transaction log I/O requests to complete. |
| CMEMTHREAD | 0x207 | This 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. |
| SHUTDOWN | 0x20A | This waittype indicates that a SHUTDOWN command has been issued by the SPID, and the SPID is waiting for active queries to complete. |
| WAITFOR | 0x20B | This waittype indicates that the SPID is sleeping because of a WAITFOR DELAY Transact-SQL statement. |
| NETWORKIO | 0x800 | This 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. |
