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.