How to Troubleshoot Waiting Tasks Without Requests in SQL Server?

Guy-Glantser, 2018-02-01

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads