Sql-server – How to get info if any Monitoring tool (DB performance Analyzer) stops working

monitoringperformanceperformance-tuningsql server

What is the Reason behind if SQL monitor for Production stopped working? what is the reason for this? I checked on Licensing, SQL server permissions, No Updates/Patching and Remote admin connections everything is alright. It might be cause of over load or OS issues effect SQL monitor? Any specific reason?

Best Answer

You are encountering THREADPOOL waits, which occurs when the instance has run out of available worker threads. New logins will be denied, and lots of queries will be just waiting.

To troubelshoot THREADPOOL waits, connect with the Remote DAC and check current activity. You'll likely see huge blocking chains. You'll need to resolve or reduce the blocking to resolve the THREADPOOL waits.

Some Microsoft engineers will say to increase worker threads on the instance, but that doesn't help in my experience. It just means it'll take longer before THREADPOOL is encountered.

Things that might resolve THREADPOOL waits:

  • Adjusting Max Degree of Parallelism and Cost Threshold for Parallelism so that less queries go parallel and thus fewer worker threads are needed
  • Adding indexes
  • Refactoring queries
  • RCSI
  • etc

For more information on THREADPOOL waits, check out these links (disclaimer: I work for Brent Ozar Unlimited):

https://www.brentozar.com/archive/2014/05/connections-slow-sql-server-threadpool/ https://www.sqlskills.com/help/waits/threadpool/ http://www.sqlpassion.at/archive/2011/10/25/troubleshooting-threadpool-waits/

Related Question