In this post, we will discuss some Dynamic Management Objects (DMO’s) to troubleshoot for Waiting Tasks.
When performance tuning queries it might be necessary to track down why a query might have been placed in the Waiter List of the SQL Server Execution Model. More specifically, what resource is the query waiting for to continue its execution? There are actually over 900 different wait types that you might run into while tuning queries.
Listed above are the four Dynamic Management Objects that we could use to track down wait type issues. The first three are dynamic management views (DMVs) and the fourth is a dynamic management function (DMF).
The sys.dm_os_waiting_tasks will be used to return information about the wait queue of tasks waiting on resources, specifically the wait duration and the actual wait types.
The sys.dm_exec_sessions will be used to return session information for the query that is waiting. This is helpful to find the program and user that is running the query, as well as, CPU time and memory usage of the query.
The sys.dm_exec_requests will be used to return information about each query that is executing. In this case, we are looking for the sql_handle value to pass into the sys.dm_exec_sql_text function to find the text of the query that is having wait issues.
Prior to running the query below, a user on Session 57 executed an UPDATE query without committing the transaction. This caused a blocking lock when a user on Session 53 subsequently tried to run a SELECT statement against the same table. To track down this issue, we will use the three DMVs and the SQL Text function.
SELECT w.session_id, w.wait_duration_ms, w.wait_type, w.blocking_session_id, w.resource_description, s.program_name, t.text, t.dbid, s.cpu_time, s.memory_usage FROM sys.dm_os_waiting_tasks as w INNER JOIN sys.dm_exec_sessions as s ON w.session_id = s.session_id INNER JOIN sys.dm_exec_requests as r ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) as t WHERE s.is_user_process = 1;
Below you can see the information that was returned. Where Session 53 has been blocked by session 57 with a Shared Lock wait type for 397,101 milliseconds.
For this demonstration, we are only showing the first four columns returned. We could show much more information using these as well as other Dynamic Management Objects to track down wait type issues.