Tracking Sqlserver Waits

Posted: March 4, 2009 in Sqlserver

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

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 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 by Wait 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)

It will clear out data that we’re not interested in and give you a fairly clean measurement:

signalWaitTimeMs  %signal waits  resourceWaitTimeMs  %resource waits

—————-  ————-  ——————  —————

          445837          17.92             2042154             82.08

Signal waits should be tiny in comparison to resource waits, so these results indicate a very heavy demand for the CPU.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s