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