I had blogged about debugging deadlocked schedulers earlier for SQL Server 2008 R2 and below releases. Since there have been some fundamental changes in SQL Server 2012, I thought it would be a good idea to post about debugging the same scenario on SQL Server 2012 instances.
I had written walkthroughs (Part 1 | Part 2) on how to troubleshoot a Deadlocked Schedulers condition for SQL Server instances. Deadlocked Schedulers is a condition where all your SQL Server worker threads are exhausted and no new work requests are being picked up by the SQL Server instance.
Starting from SQL Server 2012, the System Health extended events session tracks deadlocked schedulers condition using the extended event (scheduler_monitor_deadlock_ring_buffer_recorded). The session tracks other useful events which makes it easy to trace back the series of events which led to the deadlocked schedulers condition!
I will be using the Extended Events UI in SQL Server 2012 management studio to show how the target file of the System Health session can be used to track deadlocked schedulers condition experienced by your SQL Server instance.
I just concluded my presentation on “Debugging the Deadlock for the Scheduler” at SQL Saturday 76 over a Lync meeting. The WiFi and Lync gods decided to be benevolent today and let me present without an untoward incident! As always it was fun to demonstrate the use of a debugger…The attempt of the presentation was to demonstrate what can be done proactively and reactively when dealing with a deadlocked schedulers condition.
A special thank you is due to all the sponsors without whom the event today wouldn’t have been possible: Microsoft New Zealand, RedGate, SQL Pass, New Zealand MS Communities and SQL Services!
The slide deck used for presentation today is available on Slide Share and is embedded below:
The demo files used are available at SkyDrive in the file SQLSat76_Demo.zip The walkthrough for the debugging that I explained along with the use of the Powershell script to automatically collect DMV outputs based on messages in the Windows Application event log are available using the posts available under the walkthroughs link below as well as the zip file mentioned above.