Bila SQL server anda bermasalah salah satunya anda harus mengecek proses apa saja yang terjadi pada server tersebut. Salah satunya jenis proses Wait, berikut ini catatan untuk panduan pada kasus tersebut. Semoga bermanfaat..
SQL Server 2000 Wait Types
Wait type | Description | Comment |
ASYNC_DISKPOOL_LOCK | During backup and restore (for example, zeroing out pages), threads are written in parallel. | Possible disk bottleneck. See PhysicalDisk counters for confirmation. |
ASYNC_I/O_COMPLETION | Waiting for asynchronous I/O requests to complete.
Identify disk bottlenecks, using counters, Profiler, ::fn_virtualfilestats, and Showplan. Doing any of the following will reduce these waits:
|
See PhysicalDisk counters:
See SQL Server Buffer Manager counters for memory pressure:
See SQL Server Access Methods counters for correct indexing:
Use the system table-valued function fn_virtualfilestats to check IoStallMS value. IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. To display IoStallMS, execute the query: SELECT * FROM ::fn_virtualfilestats (dbid,file#) To list all files for a database, execute: SELECT * FROM ::fn_virtualfilestats (dbid,-1) SQL Profiler can be used to identify which Transact-SQL statements do scans. Select the Scans event category and the Scan:Started and Scan:Stopped events. Include the Object ID data column. Save the Profiler trace to a trace table, and then search for the Scans event. The Scan:Stopped event provides associated I/O so you can also search for high reads, writes, and duration. Check Showplan for bad query plans. |
CMEMTHREAD | Waiting for thread-safe memory objects. | |
CURSOR | Asynchronous cursor thread. | |
CXPACKET | Parallel process waits. Possible skew of data possible lock of a range for this CPU, meaning that one parallel process is behind, etc. | Check for parallelism using sp_configure ‘max degree of parallelism’.
If max degree of parallelism = 0, you may want to do one of the following:
|
DBTABLE | New checkpoint request is waiting for outstanding checkpoint request to complete. | See SQL Server Buffer Manager counters:
|
DTC | Waiting for Distributed Transaction Coordinator. | Check transaction isolation level. |
EC | Non-parallel synchronization between parent and child thread. | |
EXCHANGE | Waiting on a parallel process to complete, shutdown or startup. | Check for parallelism using sp_configure ‘max degree of parallelism’.
If max degree of parallelism = 0, you may want to do one of the following:
|
EXECSYNC | Query memory and spooling to disk. | |
I/O_COMPLETION | Waiting for I/O requests to complete.
Identify disk bottlenecks, using counters, Profiler, ::fn_virtualfilestats, and Showplan. Any of the following will reduce these waits:
|
See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
See SQL Server Access Methods counters for correct indexing:
See the Memory counter:
Use the system table-valued function fn_virtualfilestats to check IoStallMS value. IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. To display IoStallMS, execute the query: SELECT * FROM ::fn_virtualfilestats(dbid,file#) SQL Profiler can help identify which Transact-SQL statements do scan. Select the event category Scans and events Scan:Started and Scan:Stopped. Include the Object ID data column. Save the profiler trace to a trace table, and then search for the scans event. The Scan:Stopped event provides associated I/O so you can also search for high reads, writes, and duration. Check Showplan for bad query plans. |
LATCH_x | Short-term light-weight synchronization objects. Latches are not held for the duration of a transaction.
“Plain” latches are generally unrelated to I/O. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages (PAGELATCH_x is used for that). Possibly the most common case is contention on internal caches (not the buffer pool pages), especially when using heaps, text, or both. |
If high, check Perfmon for memory pressure or SQL Server latch waits.
Look for LOG and PAGELATCH_UP wait types. LATCH_x waits can often be improved by solving LOG and PAGELATCH_UP contention. In the absence of contention, partition the table or index in question to create multiple caches (the caches are per-index). |
LATCH_DT | Destroy latch. | See LATCH_x. |
LATCH_EX | Exclusive latch. | See LATCH_x. |
LATCH_KP | Keep latch. | See LATCH_x. |
LATCH_NL | Null latch. | See LATCH_x. |
LATCH_SH | Shared latch. | See LATCH_x. |
LATCH_UP | Update latch. | See LATCH_x. |
LCK_x | Possible transaction management issue.
|
See SQL Server Locks counter:
Check for memory pressure, which causes more physical I/O, thus prolonging the duration of transactions and locks. |
LCK_M_BU | Bulk update lock. | See LCK_x. |
LCK_M_IS | Intent share lock. | See LCK_x. |
LCK_M_IU | Intent update lock. | See LCK_x. |
LCK_M_IX | Intent exclusive lock. | See LCK_x. |
LCK_M_RIn_NL | Range intent null lock. | See LCK_x. |
LCK_M_RIn_S | Range intent shared lock. | See LCK_x. |
LCK_M_RIn_U | Range intent update lock. | See LCK_x. |
LCK_M_RIn_X | Range intent exclusive lock. | See LCK_x. |
LCK_M_RS_S | Range-shared shared (key-range) lock. | See LCK_x. |
LCK_M_RS_U | Range-shared update (key-range) lock | See LCK_x. |
LCK_M_RX_S | Range-exclusive shared (key-range) | See LCK_x. |
LCK_M_RX_U | Range-exclusive update (key-range) lock | See LCK_x. |
LCK_M_RX_X | Range-exclusive exclusive (key-range) | See LCK_x. |
LCK_M_S | Shared lock. | See LCK_x. |
LCK_M_SCH_M | Modify schema lock. | See LCK_x. |
LCK_M_SCH_S | Shared schema (stability) lock | See LCK_x. |
LCK_M_SIU | Share intent update lock. | See LCK_x. |
LCK_M_SIX | Share intent exclusive lock. | See LCK_x. |
LCK_M_U | Update lock. | See LCK_x. |
LCK_M_UIX | Update intent exclusive lock. | See LCK_x. |
LCK_M_X | Exclusive lock. | See LCK_x. |
LOGMGR | Waiting for write requests to the transaction log to complete.
Identify disk bottlenecks, using Perfmon counters, Profiler, and ::fn_virtualfilestats Doing any of the following will reduce these waits:
|
See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Use the system table-valued function fn_virtualfilestats to check IoStallMS value. IoStallMS is the cumulative number of milliseconds of I/O waits for a particular file. If IoStallMS is inordinately high for one or more files, you have a disk bottleneck. To display IoStallMS for transaction log, execute: SELECT * FROM ::fn_virtualfilestats(dbid,file#) |
MISCELLANEOUS | Catch all wait types. | |
NETWORKIO | Waiting on network I/O completion. Waiting to read or write to a client on the network.
This can occur if a client is in the middle of sending packets to SQL Server, or when SQL writes data to a client and is waiting for an ACK. |
Check bandwidth of your network interface card. 100 mbits is preferable to 10 mbs. |
OLEDB | OLEDB waits. Common causes are:
|
Check placement of client application, including any file input read by the client and SQL Server data and log files. See Disk secs/Read and Disk secs/Write. If Disk secs/Read is high, add additional I/O bandwidth, balance I/O across other drives, or put the database and transaction log on its own drives.
Inspect Transact-SQL code for RPC, Distributed (Linked Server), and Full Text Search. Although SQL Server supports these kinds of queries, they sometimes cause bottlenecks. To get the Transact-SQL statement involved in OLEDB waits, select virtual table master..sysprocesses as follows:
DECLARE @Handle binary(20) SELECT @Handle = sql_handle FROM sysprocesses WHERE waittype = 0x0042 SELECT * FROM ::fn_get_sql(@Handle)
|
PAGEIOLATCH_x | Short-term synchronization objects used to synchronize access to buffer pages. PageIOLatch is used for disk-to-memory transfers. | If the wait is significant, it normally suggests disk I/O subsystem issues. Check PhysicalDisk counters. |
PAGEIOLATCH_DT | I/O page destroy latch. | See PAGEIOLATCH_x |
PAGEIOLATCH_EX | I/O page latch exclusive. | See PAGEIOLATCH_x |
PAGEIOLATCH_KP | I/O page latch keep. | See PAGEIOLATCH_x |
PAGEIOLATCH_NL | I/O page latch null. | See PAGEIOLATCH_x |
PAGEIOLATCH_SH | I/O page latch shared. | See PAGEIOLATCH_x |
PAGEIOLATCH_UP | I/O page latch update. | See PAGEIOLATCH_x |
PAGELATCH_x | Short-term light-weight synchronization objects. Latches are not held for the duration of a transaction. Typical latching operations occur during row transfers to memory, controlling modifications to row offset table, etc. Consequently, latch duration is normally sensitive to available memory. | If the wait is significant, it normally indicates cache contention. |
PAGELATCH_DT | Page latch. | See PAGELATCH_x. |
PAGELATCH_EX | Page latch exclusive.
Contention can be caused by issues other than I/O or memory performance. For example, heavy concurrent inserts into the same index range can cause this type of contention. If many inserts need to be placed on the same page, they are serialized using the latch. Many inserts into the same range can also cause page splits in the index, which will hold onto the latch while allocating a new page (this can take a while). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate |
See PAGELATCH_x. |
PAGELATCH_KP | Page latch keep. | See PAGELATCH_x. |
PAGELATCH_NL | Page latch null. | See PAGELATCH_x. |
PAGELATCH_SH | Page latch shared.
Contention can be caused by issues other than I/O or memory performance; for example, heavy concurrent inserts into the same index range can cause this type of contention. If many inserts need to be placed on the same page they are serialized using the latch. Many inserts into the same range can also cause page splits in the index, which will hold onto the latch while allocating a new page (this can take a while). Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate |
See PAGELATCH_x. |
PAGELATCH_UP | Page latch update. Used only for allocation related pages, contention on it is often a sign that more files are needed. With multiple files, allocations can be distributed across multiple files, thus reducing demand on the per-file data structures stored on these pages. The contention is not I/O performance, but rather internal allocation contention to access the pages: adding more spindles to a file or moving the file to a faster disk will not help, nor will adding more memory. | See PAGELATCH_x. |
PAGESUPP | Waits for parallel page supplier. Possible disk bottleneck.
Doing any of the following will reduce these waits:
|
See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Check IoStallMS for database:
|
PIPELINE_INDEX_STAT | Allows one user to perform multiple operations such as writes to log cache on the user’s own behalf, as well as that of other users who are waiting for same operation. It does all log writes in single operation. | See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Check IoStallMS for database:
|
PIPELINE_LOG | Allows one user to perform multiple operations such as writes to log cache on the user’s own behalf as well as that of other users who are waiting for same operation. Does in single operation. | See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Check IoStallMS for database:
|
PIPELINE_VLM | PIPELINE wait types allow one user to perform multiple operations such as writes to log cache on the user’s behalf as well as that of other users who are waiting for same operation. Does in single operation. | See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Check IoStallMS for database
|
PSS_CHILD | Waiting on Asynch thread. | |
RESOURCE_QUEUE | Internal use only. | |
RESOURCE_SEMAPHORE | Common for DSS like workload and large queries such as hash joins; must wait for memory grant before execution. | See SQL Server Memory Manager counters:
|
SHUTDOWN | When NOWAIT is not specified, waits for other users to logout before shutdown completes. | Monitor SQL Statistics:User Connections.
To expedite shutdown, you can:
|
SLEEP | Internal use only. | |
TEMPOBJ | Dropping a global temp object that is being used by others. | |
TRAN_MARK_DT | Transaction latch – destroy. | |
TRAN_MARK_EX | Transaction latch – exclusive. | |
TRAN_MARK_KP | Transaction latch – keep page. | |
TRAN_MARK_NL | Transaction latch – null. | |
TRAN_MARK_SH | Transaction latch – shared. | |
TRAN_MARK_UP | Transaction latch – update. | |
UMS_THREAD | Batch waiting for a worker thread to free up, or batch waiting to get a worker thread to run it. | If the percentage is high, increase the number of worker threads from the default of 255. The maximum is 1024. |
WAITFOR | Inspect Transact-SQL code for WAITFOR DELAY statement. | |
WRITELOG | Waiting for write requests to the transaction log to complete.
Identify disk bottlenecks using counters, Profiler, ::fn_virtualfilestats, and Showplan. Any of the following will reduce these waits:
|
See PhysicalDisk counters:
See SQL Server Buffer Manager counters:
Check IoStallMS for transaction log:
|
XACTLOCKINFO | Transaction escalation, rollback. |
sumber sqldev.net