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.