How to monitor deadlock using extended events in SQL Server 2008 and later

How to monitor deadlock using extended events in SQL Server 2008 and later

Rate This
  • Comments 8

Before I talk about how to monitor the deadlocks let us have a small brief what is deadlock and how we were handling them in the previous versions of SQL Server.

 

Deadlock:

A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server.

 

How SQL Server resolves a deadlock?

In SQL Server Deadlock can be detected and resolved automatically without manual intervention. Here in SQL Server, Deadlock Monitor (Basically a Lock Monitor) scans through the Processes or threads every 5 seconds and if any such scenario exits, it terminates the query batch involved in that processes based upon cost of resources used.

Mentioned below are trace flags which are classical way to enable Deadlock trace in SQL Server Editions.

1.       Enable Trace Flag 1204 - Here data are captured in node level.

Dbcc traceon (T1204,-1)

2.   Enable Trace Flag 1222 - Here data is returned in XML Format. Also one can see the processes information also which are involved in deadlock.

DBCC Traceon (T1222,-1)

3.   Run SQL Profiler Trace to capture the Deadlock events.

This deadlock information can be viewed in SQL Server Error Logs and Trace Files.

The other solution to the keep continuous monitoring and capturing the deadlock information is to use extended events.



Extended Events:

SQL Server extended events are the light weight event driven (fired) data collection for SQL Servers. It is much beneficial in case of tracing in the busy servers.  It’s fundamentally an advanced version of Profiler Trace.

This article shows how to configure and monitor deadlock graph using extended events.

Till SQL Server 2008 R2 the X-events must be created using scripts. From SQL Server 2012 we can configure extended events using GUI.

By default the system health event will log the deadlock information for the instance. We can use the below sample query to list out deadlock events from the system health.

Use Master

SELECT

       xed.value('@timestamp', 'datetime') as Creation_Date,

       xed.query('.') AS Extend_Event

FROM

(

       SELECT CAST([target_data] AS XML) AS Target_Data

       FROM sys.dm_xe_session_targets AS xt

       INNER JOIN sys.dm_xe_sessions AS xs

       ON xs.address = xt.event_session_address

       WHERE xs.name = N'system_health'

       AND xt.target_name = N'ring_buffer'

) AS XML_Data

CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)

ORDER BY Creation_Date DESC

 

If we want to monitor the deadlocks explicitly, we can create a dedicated session for deadlock events. In this blog I will walk you through the steps to create sessions to monitor deadlock events.

 

Enabling Deadlock information using UI:

 

  1. Connect to SSMS and connect to the SQL Server 2012 instance. 

 

clip_image002[4]

 

 

2. There are two default sessions in extended events as shown in the above picture. Create new session wizard

 

 

          clip_image003[4]

 

         clip_image005[4]   

 

3.       Type the session name. We can start the event session at server startup by checking the check box. 

             clip_image007[4]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.       There are free defined templates for locks and blocking, system health and query execution

 

clip_image009[4]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

         

  

 

5.       We can select a template based on the requirement however for deadlock graph we will add the events manually.

      clip_image011[4]

 

 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6.       Add xml_deadlock_report event as shown in the below figure.      

      

clip_image013[4]

 

     

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

    

 

 

 

 

7.       select the global fields which are needed 

       clip_image015[4]

 

 

8.       Save the output either to file or ring buffers.

 

clip_image017[4]                     

 

 

9.       We can minimize the number of events in the ring buffers

clip_image019[4]                                 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

10.    Here is the summary of all the inputs for the event selection

 

clip_image021[4]                        

 

11.    We have created the session successfully. We can start the session immediately or we can run it on demand

 

clip_image023[4]                  

 

12.    You should be seeing now that an event which was configured is now in stopped state

clip_image025[4] 
 
 

 

clip_image026[4]

 

Creating a deadlock Scenario :

clip_image027[4]

 

Analyze the event data : event file lists all the deadlock reports with the timestamp. We can look for the specific timestamp and analyze the XML graph.

clip_image028[4]

 

 Graphical representation of deadlock:

 

clip_image030[4]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

We can click on xml report to see the xml report of the deadlock graph. 

clip_image032[4]

 

clip_image034[4]

 

These are some of steps to configure the extended events for monitoring Deadlock.

I would like to discuss about two more events which are available in X-events that can help us to drill down the deadlock issues further.

  1. Lock: Deadlock Event Class

    This class can be used to identify the deadlock victim. This event rises whenever the request to acquire a lock is cancelled as this has been chosen as a deadlock victim.

   clip_image036[4]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.   Lock: Deadlock chain

This event class is also used to monitor the deadlock scenarios. This event will be raised whenever there is a deadlock situation. By monitoring this events at the instance level, we can identify which objects are involved in deadlock and do we have any performances issues in the application due to deadlocks.

 

 

clip_image038[4]

 

Using these two event classes two monitor deadlocks in the SQL server :

 

clip_image040[4]

 

 

clip_image042[4]

 

 

 

clip_image044[3]

 

 

 

clip_image045[3]

 

 

clip_image047[3]

 

 

We can watch the live data from these units by checking the check box in the above screenshot.

 

Below example shows a simple deadlock scenario:

 

clip_image048[3]

 

 

Looking at the extended events live data

 

clip_image049[3]

 

 

clip_image051[3]

 

 

If any users comes with issue saying “There was a deadlock reported in one of Application error log” which has occurred midnight now you know where we can find the data.  

 

Author : Sravani Saluru, Support Engineer, Microsoft India GTSC

Reviewed by:

Karthick PK, Technical Lead, Microsoft India GTSC

Amit Khandelwal, Technical Lead, Microsoft India GTSC

 

Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
  • Since the xml_deadlock_report is a part of the system_health session and has been since 2008, why would you need to create another session to do the same thing?  Even on really busy systems the event definition of system_health minimizes what events are being collected and would allow the deadlock information to still exist in the ring_buffer typically, and if not there, definitely in the event_file data for the event session.  

  • Yes, I have also mentioned about the default system health event in the blog. However if we want to monitor deadlocks specifically and have a customized targets we can use the above.

  • Will the dbcc traceon command work with the T before the trace flag number?

    1.       Enable Trace Flag 1204 - Here data are captured in node level.

    Dbcc traceon (T1204,-1)

    2.   Enable Trace Flag 1222 - Here data is returned in XML Format. Also one can see the processes information also which are involved in deadlock.

    DBCC Traceon (T1222,-1

  • Exhaustive article ...!!!

  • Very useful article :) Clearly described.

  • Do you have the sql scripts to create the extended events to monitor deadlocks for sql server 2008 R2?

  • Thanks for the script

  • I am unable to see the Extended Events drop down. I am using SQL Server 2008 R2. Please let me know how to add it/install it.

Page 1 of 1 (8 items)