Following several real-world use cases where high THREADPOOL waits and Deadlocked Scheduler incidents occurred in production environments, I gathered enough experience to create a practical guide for troubleshooting such cases:
Excerpt:
In short, high THREADPOOL waits can happen when SQL Server doesn’t have enough “worker threads” to handle new tasks, which could cause SQL Server to hang and refuse connections.
A background process, called “Scheduler Monitor“, will identify when the same worker threads are “stuck” in the same state for 60 seconds or more. In which case it will resolve the issue as a Deadlocked Scheduler, and that’ll cause dropped connections, rollbacks, and even fail-overs.
When a Deadlocked Scheduler event happens, SQL Server will automatically generate a memory dump file (SQLDump#####.mdmp), and log the incident in the SQL Server Error Log.
In the post, I provide various possible solutions and recommendations to troubleshoot scenarios of high THREADPOOL waits and Deadlocked Schedulers.
I also provide a nifty T-SQL script to help you troubleshoot these incidents retroactively, and easily pinpoint the actual root cause.
Comments