SQLServer数据库的死锁分析

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


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

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



SELECTs.name , se.event_name, s.create_time
--,CAST(target_data AS XML) AS Target_Data
FROMsys.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会话的而设置

*/
    
SELECTes.name, esf.name, esf.value 
FROM sys.server_event_session_fields ASesf 
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中的死锁信息。
*/

SELECTDeadlockGraph
    , [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 ) ASDeadlockGraph   
    FROM( 
        SELECT XEvent.query('.') ASevent_data 
        FROM ( --Cast the target_data to XML
             SELECT CAST( st.target_data AS XML ) ASTargetData 
             FROMsys.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') ASData        
        CROSS APPLY      --Split out the Event Nodes         
        TargetData.nodes ( 'RingBufferTarget/event[@name="xml_deadlock_report"]') ASXEventData ( XEvent )   
    ) AStab ( event_data )
) AS A ORDER BY [LastTranStarted];