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

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
sys.dm_os_waiting_tasks - AllWaiting 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 for as long as it's waiting, so
sys.dm_os_waiting_tasks tends to be used for interactive investigations rather than for monitoring
purposes. You can use the columns that report on blocking tasks to identify blocking locks, which is
discussed toward the end of the chapter. Here is a sample script that shows all the information for waiting
tasks with the T-SQL currently running when a session_id is available:
SELECT wt.*,
st.text
FROM sys.dm_os_waiting_tasks wt LEFT JOIN sys.dm_exec_requests er
ON wt.waiting_task_address = er.task_address
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
ORDER BY wt.session_id
sys.dm_os_wait_stats - Aggregated Times
byWait Type
This DMV is an aggregation of all wait times from all queries since SQL Server started and is ideal
for monitoring and server-wide tuning. You can reset the wait statistics by running DBCC sqlperf
( 'sys.dm_os_wait_stats', clear ). The following sample script from Microsoft is a great way to check
for CPU pressure by comparing signal wait times (CPU wait) with resource wait times:
Select signalWaitTimeMs=sum(signal_wait_time_ms)
,'%signal waits' = cast(100.0 * sum(signal_wait_time_ms) / sum
(wait_time_ms) as numeric(20,2))
,resourceWaitTimeMs=sum(wait_time_ms - signal_wait_time_ms)
,'%resource waits'= cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) /
sum (wait_time_ms) as numeric(20,2))
from sys.dm_os_wait_stats
If you want to clear the historical data before you run the load to monitor, run this:
DBCC sqlperf ('sys.dm_os_wait_stats',clear)