Analyze the deadlock using the default system_health (deadlock)

Before 2008, we analyzed the deadlock and needed to use Profiler trace or trace flag 1222,120. 4. introduced a new feature in 2008: Extended events, which can monitor deadlock events and provide better performance.

 

In addition, 2008 comes with a default extended Event session system_health. If you are running version 2008 or later, you can execute the following query:

 

Select * From SYS. dm_xe_sessions

 

Among them, system_health collects a lot of important information and can be used for analysis if any problem occurs. For information about system_health session collection, see http://msdn.microsoft.com/en-us/library/ff877955.aspx. One of the items is: Any deadlocks that are detected.

 

 

That is, SQL Server automatically collects the deadlock information and records it in ring_buffer. By analyzing ring_buffer, you can find the cause of the deadlock, so you do not need to use profiler or trace flag to collect information.

 

Use the following code to view the deadlock_report content:

 

SELECT    xed.value('@timestamp','datetime')as Creation_Date,         xed.query('.')AS Extend_Event FROM    (   SELECT   CAST([target_data]ASXML)AS Target_Data             FROM    sys.dm_xe_session_targetsAS xt                     INNER JOIN sys.dm_xe_sessionsAS 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_DateDESC

 

 

 

 

The default system_health record important events without affecting the performance, which is a very good function for us to analyze later. This avoids the possibility that the cause cannot be found due to the lack of timely monitoring.