932
Part VII: Monitoring and Auditing
As tasks run inside the database engine, they typically follow a circular pattern that jumps
in and out of three main states:■ (^) Runnable: Workers in a runnable status are waiting for time to execute on the
scheduler. These can be workers that have previously been suspended, or workers
executing new requests waiting to be executed. Time spent in the runnable queue
is pure CPU wait time. This is also known as signal wait time because the tasks are
waiting for their “signal” to run. Workers in the runnable queue eventually get to
the scheduler so that they can execute.
■ (^) Running: Workers with a running status are currently executing a task. Only one
worker thread can be running on a scheduler at any given time. The worker runs
until it needs something that it must wait for, or until it removes itself from the
scheduler. If the worker must wait on a resource, it is moved to the waiting list and
is assigned the status of suspended. If it removes itself from the CPU, it is placed
back on the runnable queue.
■ (^) Suspended: Workers waiting on something are fl agged with a status of suspended.
Suspended tasks are in the “waiting list” because they are waiting for something.
As the resources that the process is waiting on become available, the process moves
from the waiting list to the runnable queue.
When a worker must wait for a resource, such as CPU (signal wait time), IO, a lock, or mem-
ory, SQL server can track how long that process waits for that particular resource. You can
use this data to see where SQL Server spends its time waiting and research to see if the root
cause of the wait durations is a problem.
Examining Wait Statistics
You can retrieve wait statistic information in many ways. In this chapter, you examine
three different dynamic management views (DMV) to use to gather this information.Executing Requests
To view all tasks currently executing, use the DMV sys.dm_exec_requests (see Figure 39-1).
This DMV includes information for all tasks that are currently executing (running), cur-
rently waiting on a resource (suspended), or currently waiting their turn to get on the
scheduler to execute (runnable). Use this DMV for a high-level overview of all tasks cur-
rently executing on your SQL Server Instance.Wait Stats
The DMV sys.dm_os_wait_stats (see Figure 39-2) returns the aggregated time waited, by wait
type, since the last time SQL Server was restated or the last time the DMV was cleared. Use
this DMV to see a holistic view of all the wait times on your system to see where SQL Server
spends its time waiting. Many waits returned in this DMV are benign waits and should bec39.indd 932c39.indd 932 7/31/2012 10:04:44 AM7/31/2012 10:04:44 AM
http://www.it-ebooks.info