935
Chapter 39: Wait States
39
Common Red-Flag Wait Types
The following table lists common wait types that warrant investigation if they represent a
high overall percentage of system waits.Wait Name Description
CXPACKET Time spent waiting for thread synchronization to complete a
parallel query. This often happens when one thread must per-
form more work than other threads to complete a single state-
ment. High values here may be an indication that queries can be
optimized for OLTP systems.
PAGEIOLATCH_x Time spent waiting for a latch on a memory buffer for an IO
request. High waits of this type may indicate a problem with the
disk subsystem, large table scans, or not enough memory.
PAGELATCH_x Time spent waiting for a latch that is not IO-related. This
sometimes manifests itself as heavy inserts into the same page,
which is often caused by heavy inserts on a table with a clustered
index on an identity column. This is sometimes seen when there
is a hot-spot on allocation pages within tempdb.
LCK_x Time spent waiting for a lock to be acquired. High waits here
could indicate poorly written queries that are taking locks and are
long running, or potential problems with slow IO subsystems.
WRITELOG/LOGBUFFER Time spent waiting for a log fl ush to complete, and time spent
waiting for space to store a log record in a log buffer, respec-
tively. Issues with either of these waits may indicate that your log
fi le is not on the correct type of media or that your log is sharing
IO with other fi les on its drive.
ASYNC_NETWORK_IO Time SQL Server spends waiting for the client to process the data
it has sent before it can send more. This typically occurs when a
program runs a query that returns a large result set that it must
consume.
SOS_SCHEDULER_YIELD Time a worker spends waiting for its time-slice to be renewed.
Occurs when a task removes itself from the scheduler so that
other tasks can execute. High values here likely indicate CPU
pressure.
RESOURCE_SEMAPHORE Time spent waiting for a workspace memory request for a query
to be granted. High values of this wait may indicate a large
number of resource intensive queries, typically using hash-joins
or sorts, or a potential memory bottleneck.c39.indd 935c39.indd 935 7/31/2012 10:04:46 AM7/31/2012 10:04:46 AM
http://www.it-ebooks.info