sys.dm_os_waiting_tasks – Day 18 – One DMV a Day

Hello Geeks and welcome to the Day 18 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Yesterday in my blog on sys.dm_os_wait_stats I have mentioned that we can check the waiting state of single task. Sys.dm_os_waiting_tasks is the DMV which exposes this information. This DMV is useful when you are troubleshooting an ongoing issue on a SQL instance. I choose this DMV to keep the minds calm for the Monday mornings and keep it simple.

Sys.dm_os_waiting_tasks shows the session id, blocking session id if any, wait time on current wait type, resource description on which it is waiting. The query is straight forward and can be joined with other DMVs to get more information.

Let us run the below query where I join sys.dm_os_waiting_tasks with two other DMVs to get the current statement and status of the session. I have created a simple case of blocking on my machine and below is the output from the query.

SELECT owr.session_id,
	owr.wait_duration_ms,
	owr.wait_type,
	owr.blocking_session_id,
	owr.resource_description,
	er.wait_resource,
	er.command,
	er.status,
	est.text
FROM sys.dm_os_waiting_tasks owr
INNER JOIN sys.dm_exec_requests er
ON owr.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
WHERE owr.session_id > 50

In the above output you will see the resource description is much detailed in sys.dm_os_waiting_tasks than the wait_resource from exec requests. It gives you detailed information based on the resource type. In this case it was a key lock and the hobtid, database id and lock id are given. It also gives the mode of lock like X for exclusive in our case. More information on the resource description is provided here.

Tomorrow I will talk about another OS related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook