Analyze the deadlock using the default system_health (deadlock)

Source: Internet
Author: User

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 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'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.



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.