Ciqbal’s Weblog

November 7, 2009

SQL Server 2000 Wait Types

Filed under: sql server, Technology — ciqbal @ 4:13 pm

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:

  • Adding additional I/O bandwidth.
  • Balancing I/O across other drives.
  • Reducing I/O with proper indexing.
  • Checking for bad query plans.
  • Checking for memory pressure.
See PhysicalDisk counters:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters for memory pressure:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

See SQL Server Access Methods counters for correct indexing:

  • Full Scans/sec
  • Index Searches/sec

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:

  • Turn off parallelism by setting max degree of parallelism to 1
  • Limit parallelism by setting max degree of parallelism to less than the total number of CPUs. For example, if you have 8 procedures, set max degree of parallelism to 4 or less.
DBTABLE New checkpoint request is waiting for outstanding checkpoint request to complete. See SQL Server Buffer Manager counters:

 

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec
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:

  • Turn off parallelism entirely by setting max degree of parallelism to 1
  • Limit parallelism by setting max degree of parallelism to less than the total number of CPUs. For example, if you have eight procedures, set max degree of parallelism to 4 or less.
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:

  • Adding additional I/O bandwidth.
  • Balancing I/O across other drives.
  • Reducing I/O with proper indexing.
  • Check for bad query plans.
See PhysicalDisk counters:

 

  • Disk Sec/read
  • Disk Sec/write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

See SQL Server Access Methods counters for correct indexing:

  • Full Scans/sec
  • Index Searches/sec

See the Memory counter:

  • Page Faults/sec

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.

 

  • For shared locks, check Isolation level for transaction.
  • Keep transaction as short as possible.
See SQL Server Locks counter:

 

  • Lock Wait Time (ms)

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:

  • Adding additional I/O bandwidth.
  • Balancing I/O across other drives.
  • Placing the transaction log on its own drive.
See PhysicalDisk counters:

 

  • Disk Sec/read
  • Disk Sec/write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

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:

 

  • SQL Server is waiting for client application to send data.
  • A linked server or remote procedure call (RPC).
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:

  • SQL2000 Service Pack 3 Only

DECLARE @Handle binary(20)

SELECT @Handle = sql_handle FROM sysprocesses

WHERE waittype = 0x0042

SELECT * FROM ::fn_get_sql(@Handle)

  • SQL2000 RTM, SP1, and SP2 (limited to 255 characters), run dbcc inputbuffer (spid)
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:

  • Adding additional I/O bandwidth.
  • Balancing I/O across other drives.
  • Reducing I/O with proper indexing.
  • Checking for bad query plans.
See PhysicalDisk counters:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

Check IoStallMS for database:

  • SELECT * FROM ::fn_virtualfilestats(dbid,file#)
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:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

Check IoStallMS for database:

  • SELECT * FROM ::fn_virtualfilestats(dbid,file#)
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:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

Check IoStallMS for database:

  • SELECT * FROM ::fn_virtualfilestats(dbid,file#)
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:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

Check IoStallMS for database

  • SELECT * FROM ::fn_virtualfilestats(dbid,file#)
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:

 

  • Memory Grants Pending
  • Memory Grants Outstanding
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:

  • Run SHUTDOWN WITH NOWAIT.
  • Use the KILL command to terminate user connections.
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:

  • Adding additional I/O bandwidth.
  • Balancing I/O across other drives.
  • Placing the transaction log on its own drive.
See PhysicalDisk counters:

 

  • Disk sec/Read
  • Disk sec/Write
  • Disk Queues

See SQL Server Buffer Manager counters:

  • Page Life Expectancy
  • Checkpoint Pages/sec
  • Lazy Writes/sec

Check IoStallMS for transaction log:

  • SELECT * FROM ::fn_virtualfilestats(dbid,file#)
XACTLOCKINFO Transaction escalation, rollback.

sumber sqldev.net

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: