Troubleshooting Deadlocks with Extended Events

Recently I started getting random alerts that a job on one of the SQL servers was failing because of a deadlock problem.

The source of the problem wasn’t immediately discernible as there wasn’t any pattern to when the job was failing. Troubleshooting was further complicated by the database being written/maintained by a 3rd party vendor that encrypts all of their stored procedures.

So… How to find out what was causing the deadlock?

Extended Events are an ideal solution for this situation.

Creating a new Extended Event session:

1. In SQL Server Management Studio (SSMS), expand the Management and Extended Events folders, right-click on Sessions and select New Session.
   
2. On the General page, enter a descriptive Session name, and select the ‘Start the event session at server startup‘ and ‘Start the event session immediately after session creation‘ options.
   
3. On the Events page, add the following events from the Event library (on the left) to the list of Selected events (on the right).
   • blocked_process_report
   • database_xml_deadlock_report
   • lock_deadlock
   • lock_deadlock_chain
   • scheduler_monitor_deadlocks_ring_buffer_recorded
   • xml_deadlock_report

   

4. Click the Configure button to select the Global Fields (Actions) to be recorded for each event.
   • client_app_name
   • client_hostname
   • context_info
   • database_name
   • nt_username
   • sql_text
   • username

   

5. On the Data Storage page select the event_file target type to write the event data to a file.
   

   

6. Click on OK to close the window and begin data collection.

To view the Extended Event session log:

1. Expand the Deadlocks session, right-click on the event file, and select View Target Data.
   

   

2. Scroll to the bottom of the event list, select the most recent xml_deadlock_report or database_xml_deadlock_report, and double-click on the xml_report in the Details section. This will open the xml report in a new tab.
   

   

The xml report will show which actions are causing the deadlock. In this case, the report’s process-list shows two stored procedures (Alert.MissingLog and Collector.LogInfoUpdate). Looking further down, the resource-list shows that the Collector process has an exclusive lock on the object HVAC.Collector.LogDetails, and the Alert process is requesting a shared lock on the same table.

Deadlock Remediation

The last step is to send this information to the developers (since both stored procedures are encrypted) for further troubleshooting & remediation.