SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 70

This wait represents time spent pending a memory grant in workspace memory and should
be correlated with the Memory Grants Pending Performance Monitor counter. A consistent
non-zero value will indicate memory pressure and the wait time will tell you how much time
you are losing.

❑ RESOURCE_SEMAPHORE_QUERY_COMPILE: This wait type is set when SQL Server

throttles the number of concurrent compiles in the system to limit the amount of memory being
used by the optimizer in response to too many compilation requests. If you see this wait type,
reduce the number of compilations by parameterizing queries so that the query plans can be
re-used in cache.

❑ SOS_SCHEDULER_YIELD: Occurs when a task voluntarily yields processer time and waits

to be scheduled again. This cooperative scheduling model was explained in the Architecture
section. High waits here indicate CPU pressure and further evidence should be obtained by
totaling the signal waits (using signal_wait_time_ms column in sys.dm_os_wait_stats ).

CXPACKET: This wait type means that the task is waiting on the synchronization of a parallel
execution and is very often the reason for an ''all processors running at 100 percent'' scenario.
On an OLTP system you shouldn't see this at all unless you're doing a large index rebuild.
Setting Max Degree of Parallelism equal to the number of physical processers is a general
Microsoft recommendation to avoid 100 percent CPU scenarios.

❑

I/O_COMPLETION, ASYNC_I/O_COMPLETION: These wait types occur when waiting
for non-data page I/Os to complete and you may see them during a long running I/O
bound operation such as BACKUP. These wait types can also indicate a disk
bottleneck.

❑

PAGEIOLATCH_*: A PAGEIOLATCH is generally a measure of the time it takes to retrieve a
data page from disk into memory and is one of the most likely waits you'll see on a system with a
strained I/O subsystem.

❑

WRITELOG: This also indicates a disk problem as it's a wait on the transaction log file that
occurs during checkpoints or transaction commits.

❑

❑ LCK_M_*: This wait type indicates a wait to gain a lock on a resource and is covered in the

''Locking and Blocking'' section later in this chapter.

How to Track Waits
There are three DMVs available that allow you to view waits directly. You can use sys.dm_exec_
requests to view session-level information. The sys.dm_os_waiting_tasks DMV allows you to see
information at the task level. The sys.dm_os_wait_stats DMV shows you an aggregation of wait times.

sys.dm_exec_requests - Session Level InformationOnly
This DMV shows all the waiting and blocking information that you would have queried sysprocesses
for in SQL Server 2000. However, both sysprocesses and sys.dm_exec_requests are based at the
session level, and a better view of performance can be obtained by looking at the task level. System
processes can run tasks without a session, so they wouldn't be represented here, and parallel queries
are harder to troubleshoot when only a single wait is shown at the session level. Following is a