SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to Troubleshoot Waiting Tasks Without Requests in SQL Server?

Usually, when I need to troubleshoot currently running requests, I use a combination of sys.dm_exec_requests and sys.dm_os_waiting_tasks. The former retrieves all the current requests, whether they are currently running or waiting. The latter retrieves all the current waiting tasks. So if a request is currently waiting, it will have one or more associated rows in sys.dm_os_waiting_tasks. And if it is not currently waiting, then sys.dm_os_waiting_tasks will show nothing for that request. This is why I always use a LEFT OUTER JOIN between sys.dm_exec_requests and sys.dm_os_waiting_tasks.


I have just learned that sometimes a waiting task can appear in sys.dm_os_waiting_tasks without a corresponding request. This happens when the system is out of worker threads, and then the next task that needs to run is waiting on the THREADPOOL wait type. There is no request yet, because a request is established only when a worker thread is assigned to the task. I learned this from this excellent post by Klaus Aschenbrenner about troubleshooting THREADPOOL waits.


So next time I need to troubleshoot currently running requests, I’m going to replace that LEFT OUTER JOIN with a FULL OUTER JOIN.

The post How to Troubleshoot Waiting Tasks Without Requests in SQL Server? appeared first on Madeira Data Solutions.

Guy Glantser

Guy Glantser, Data Platform MVP, is the leader of the Israeli PASS chapter and also the CEO and founder of Madeira Data Solutions. His career has been focused on the Microsoft Data Platform for the past 20 years, performing various database roles as either an on-site DBA, an external consultant or a speaker. Guy is involved in many activities in the Microsoft Data Platform community. He occasionally speaks at community events, such as PASS Summit, SQLBits, SQL Saturdays and user groups around the world. He also co-hosts the SQL Server Radio podcast.


Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...