The Destination for Database Solutions

Wait Type

Category

Description

Correlation to Other info

ASYNC_DISKPOOL_LOCK

IO (Restore DB)

RARE During Backup and Restore (e.g. including zeroing out pages) threads written in parallel. 

Possible disk bottleneck.  See disk perf counters for confirmation.

ASYNC_IO_COMPLETION

IO

Waiting for asynchronous IO requests to complete. 

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Reducing IO with proper indexing

4. Check for bad query plans

5. Check for memory pressure  

See PERFMON Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See PERFMON SQL Buffer Cache perf counters for memory pressure:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

See PERFMON SQL Access Methods for  correct indexing:

1. Full Scans/sec

2. Index seeks/sec

Check IoStallMS – IoStallMS is the number of cumulative milliseconds of IO waits for a particular file.  If IoStallMS is inordinately high for one or more files, you have a disk bottleneck.

1.      select * from ::fn_virtualfilestats (dbid,file#)

2.      select * from ::fn_virtualfilestats (dbid,-1) to list all files for a database. 

SQL Profiler can be used to identify which TSQL statements do scans.  Select the scans event class & events scan:started and scan:completed. Include the object Id data column.  Save the profiler trace to a trace table, and then search for the scans event.  The scan:completed event will provide associated IO 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

Asynch Cursor thread

CXPACKET

Parallel process waits.  Possible skew of data possible lock of a range for this cpu meaning one parallel process is behind, etc.

In an OLTP environment, excessive CXPACKET waits can impact the throughput of other OLTP traffic.

In a DW environment, CXPACKET waits are expected for multiple proc environments.

Check for parallelism – sp_Configure “max degree of parallelism”. 

If max degree of parallelism = 0, you may want to do one of the following:

1. turn off parallelism entirely: set max degree of parallelism to 1

2. limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs.  For example if you have 8 procs, set max degree of parallelism to <=4.

DBTABLE

New Checkpoint request that is waiting for outstanding checkpoint request to complete

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/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 – sp_Configure “max degree of parallelism”. 

If max degree of parallelism = 0, you may want to do one of the following:

1. turn off parallelism entirely: set max degree of parallelism to 1

2. limit parallelism by setting max degree of parallelism to some number less than the total number of CPUs.  For example if you have 8 procs, set max degree of parallelism to <=4.

EXECSYNC

Query memory and spooling to disk

IO_COMPLETION

IO

Waiting for IO requests to complete. 

Identify disk bottlenecks, using PERF Counters, Profiler,

::fn_virtualfilestats and SHOWPLAN

Any of the following will reduce these waits:

1. Adding additional IO bandwidth,

2. Balancing IO across other drives

3. Reducing IO with proper indexing

4. Check for bad query plans

See Disk perf counters:

1. Disk sec/read

2. Disk sec/write

3. Disk queues

See SQL Buffer Cache perf counters:

1. Page Life Expectancy

2. Checkpoint pages/sec

3. Lazywrites/sec

See SQL Access Methods for  correct indexing:

1. Full Scans/sec

2. Index seeks/sec

See memory perf counter

1. Page faults/sec

Check IoStallMS

1. select * from ::fn_virtualfilestats(dbid,file#)

SQL Profiler can be used to identify which TSQL statements do scan.  Select the scans event class & events scan:started and scan:completed. Include the object Id data column.  Save the profiler trace to a trace table, and then search for the scans event.  The scan:completed event will provide associated IO so you can also search for high reads, writes, and duration.

Check SHOWPLAN for bad query plans

LATCH_x

Latch

Latches are short term light weight synchronization objects.  Latches are not held for the duration of a transaction.

“Plain” latches are generally not related to IO.  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 and/or text.  

If high, check PERFMON for

1. memory pressure

2. SQL Latch waits (ms)

Look for LOG and Pagelatch_UP wait types.

Latch_x waits can often be alleviated by solving LOG and PAGELATCH_UP contention.  In the absence of LOG and/or PAGELATCH_UP contention, the only other option is to partition the table/index in question in order to create multiple caches (the caches are per-index). 

LATCH_DT

Latch

Destroy Latch

See LATCH_x

LATCH_EX

Latch

Exclusive Latch

See LATCH_x

LATCH_KP

Latch

Keep Latch

See LATCH_x

LATCH_NL

Latch

Null Latch

See LATCH_x

LATCH_SH

Latch

Shared Latch

See LATCH_x

LATCH_UP

Latch

Update Latch

See LATCH_x

LCK_x

Lock

Possible transaction management issue. 

1. For shared locks, check Isolation level for transaction. 

2. Keep transaction as short as possible

See SQL Locks perf counters

1.Lock wait time (ms)

Hint: check for memory pressure, which causes more physical IO, thus prolonging the duration of transactions and locks.

LCK_M_BU

Lock