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

sys.dm_os_waiting_tasks

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

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published. Required fields are marked *