Contact us

Madeira Data Solutions

Your Data, Our Solutions

How to Troubleshoot Waiting Tasks Without Requests in SQL Server?

Written By: Guy Glantser 01/02/2018

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.

4 responses to “How to Troubleshoot Waiting Tasks Without Requests in SQL Server?”

  1. Alex Friedman says:

    Interesting. Does Adam Machanic’s sp_whoisactive catch this?

    • Guy Glantser says:

      Good question. I don’t know.
      But I will check…

      • Guy Glantser says:

        Adam’s response: “No, it’s not designed for that. Those waits are something you want to monitor for separately from normal activity.”

    • No — sp_whoisactive does not report these. And it’s not something you’d want to see there in any case, as there is basically NO ancillary information available. These waits usually occur during the pre-connect process, so there is no session id, no login name, no host name, etc. This is a great example of a wait that you should be monitoring at the instance level, and set up some alerting for. If these waits get heavy, you’ll find out — users will start getting login timeouts. By the time you’re running an ad hoc DMV query or something like sp_whoisactive, you’re already too late.

Leave a Reply

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