Professional SQL Server® 2005 Performance Tuning

4.4. 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.

4.4.1. sys.dm_exec_requests – Session Level Information Only

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 sample script that shows wait information and the T-SQL currently running in each session where available:

SELECT    er.session_id,
          er.database_id,
          er.blocking_session_id,
          er.wait_type,
          er.wait_time,
          er.wait_resource,
          st.text
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

4.4.2. sys.dm_os_waiting_tasks – All Waiting Tasks

sys.dm_os_waiting_tasks lists all tasks that are currently waiting on something and is the most accurate for viewing current waits. It contains information to identify a task, an associated session, details of the wait, and blocking tasks as well. However, a task only has an entry ...