Microsoft SQL Server 2012 Bible

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), currently 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 currently executing on your SQL Server Instance.

Figure 39.1 Querying sys.dm_exec_requests for any request that has a status of running, suspended, or runnable. The query returns the current wait type of the request as well as the last wait type, and the total elapsed time for the request.

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 be ignored, even if they are high on the waiting list. Later in this chapter you review some common waits to watch if they represent a high percentage of wait time on your ...