When I run sp_whoisactive throughout the day, I usually see we have around 500-700 connections on our server. We are occasionally getting threadpool waits and high signal wait ratios (so we are thread starved at times). However, of these 500-700 connections only 10-25 of them are active. I wasn't sure if a sleeping spid is still bound to a thread while it sleeps? Could that be causing our high signal wait ratio and threadpool waits? I have a feeling our 3rd party application is not properly closing out connections and is causing us issues, but wanted to check first before I bring this up with them. Thanks!

  • Have a look at this question. Make sure you have max dop set away from default value. – Kin Shah Nov 5 '15 at 15:49
  • Yes it already is. What I really want to know is do sleeping sessions hold a thread? – Chris Woods Nov 5 '15 at 16:49

I suppose they could be bound to a thread while they are inactive, but they shouldn't be causing threadpool waits if they are just sitting there. I'd look at what those active sessions are doing. See if they have a "CXPACKET" wait type, that could show that those active sessions are using multiple threads to perform their actions. Here is a good article about CXPACKET with some troubleshooting tips: https://www.mssqltips.com/sqlservertip/2027/a-closer-look-at-cxpacket-wait-type-in-sql-server/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.