SQLServer数据库的死锁分析

--分析死锁语句
--其中时间加八小时为发生死锁时间

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

以下来自于博客 https://www.cnblogs.com/kerrycode/p/10910538.html

/*
——以下来自于博客
    https://www.cnblogs.com/kerrycode/p/10910538.html


如果要确认system_health会话是否在启动时是否启动会话，可以使用下面脚本：
startup_state：0 (OFF)。 服务器启动时不启动会话。
               1 (ON)。  服务器启动时启动事件会话
*/

SELECT es.name, esf.name, esf.value, es.startup_state
FROM sys.server_event_session_fields AS esf
JOIN sys.server_event_sessions AS es ON esf.event_session_id=es.event_session_id
WHERE  es.name='system_health'



SELECT  s.name , se.event_name, s.create_time
--,CAST(target_data AS XML) AS Target_Data
FROM sys.dm_xe_sessions s 
INNER JOIN sys.dm_xe_session_events se ON ( s.address = se.event_session_address ) 
    AND ( event_name = 'xml_deadlock_report' )
WHERE name = 'system_health';


/*

默认情况下，system_health会话使用
    环形缓冲区目标（ring buffer target）和事件文件目标（event file target）存储数据。
    
事件文件目标配置为最大大小为 5 MB，文件保留策略为4个文件;
在SQL Server 2008或 SQL Server 2008R2下，默认使用环形缓冲区目标（ring buffer target）;
而SQL Server 2012或以上版本默认使事件文件目标（event file target）存储数据。

如下脚本所示，你可以使用下面脚本查看system_health会话的而设置

*/
    
SELECT es.name, esf.name, esf.value 
FROM sys.server_event_session_fields AS esf 
JOIN sys.server_event_sessions AS es ON esf.event_session_id=es.event_session_id
WHERE es.startup_state=1 AND es.name='system_health' 
    AND (esf.name= 'filename' OR esf.name ='max_file_size' OR esf.name='max_rollover_files')



/*
system_health会话分析死锁
如果system_health会话使用ring buffer target保存事件数据的话，那么你需要知道下面这些内容：
ring buffer target将事件数据保存到内存中，事件数据以XML格式存储。一旦事件数据把分配的内存Buffers 用尽，那么最老的事件数据将被清除。
ring buffer target简单地把数据存储到内存中，这种target模式采用两种模式来管理事件：
第一种模式是严格地先进先出（first-in first-out ，FIFO），也就是说，当分配的内存被target耗尽时，从内存中移除创建时间最早的事件。
第二种模式是per-event 先进先出模式，也就是说，每一种类型的事件都持有一个计数。在这种模式下，当分配的内存被target耗尽时，每个类型中创建时间最早的事件从内存中被移除。
我们可以使用下面脚本来查看ring buffer target中的死锁信息。
*/

SELECT DeadlockGraph
    , [DbId] = DeadlockGraph.value ( '(/deadlock/resource-list//@dbid)[1]', 'int' )
    , [DbName] = DB_NAME ( DeadlockGraph.value ( '(/deadlock/resource-list//@dbid)[1]', 'int' ) ) 
    , [LastTranStarted] = DeadlockGraph.value ( '(/deadlock/process-list/process/@lasttranstarted)[1]', 'datetime' )
FROM(   
    SELECT CAST ( event_data.value ( '(event/data/value)[1]', 'varchar(max)' ) AS XML ) AS DeadlockGraph   
    FROM ( 
        SELECT XEvent.query('.') AS event_data 
        FROM ( -- Cast the target_data to XML
             SELECT CAST( st.target_data AS XML ) AS TargetData 
             FROM sys.dm_xe_session_targets st 
             JOIN sys.dm_xe_sessions s ON s.[address] = st.event_session_address 
             WHERE name = 'system_health' AND target_name = 'ring_buffer' 
        ) AS Data        
        CROSS APPLY      -- Split out the Event Nodes         
        TargetData.nodes ( 'RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent )   
    ) AS tab ( event_data )
) AS A ORDER BY [LastTranStarted];