SQL Server Wait Types, Perfmon Counters, and Correlations
When troubleshooting performance problem with Microsoft SQL Server, you might come across various waits and resource contentions as indicated in WAIT_TYPE column in sysprocesses table.
The following is an extract from published white paper from Microsoft.
Wait Types and correlation to other Performance info
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 |