SQL Server扩展事件system_health会话总结

来源：cnblogs　　作者：潇湘隐者　　时间：2019/5/24 10:08:20　　对本文有异议

system_health会话概念

我们知道扩展事件（Extended Events）是从SQL Server 2008开始引入的。system_health会话是SQL Server默认包含的扩展事件会话。该会话在SQL Server数据库引擎启动时自动启动，并且运行时不会对性能造成任何明显影响。该会话收集的系统数据可用于帮助对数据库引擎的性能问题进行故障排除。

该会话收集的信息包括：（下面来自官方文档“使用 system_health 会话”）

· 发生严重性 >=20 的错误的任何会话的 sql_text 和 session_id 。

· 发生内存相关错误的任何会话的 sql_text 和 session_id。这些错误包括 17803、701、802、8645、8651、8657 和 8902。

· 任何无法完成的计划程序问题的记录。这些问题在 SQL Server 错误日志中显示为错误 17883。

· 检测到的任何死锁，包括死锁图形。

· 等待闩锁（或其他相关资源）时间 > 15 秒的任何会话的 callstack、sql_text 和 session_id。

· 等待锁（或其他相关资源）时间 > 30 秒的任何会话的 callstack、sql_text 和 session_id。

· 为获得“抢先等待”（或其他相关资源）而等待时间很长的任何会话的 callstack、sql_text 和 session_id。持续时间因等待类型而异。在抢先等待中， SQL Server 等待的是外部 API 调用。

· CLR 分配失败和虚拟分配失败的调用堆栈和 session_id。

· 有关内存代理、计划程序监视、内存节点 OOM、安全性和连接的环形缓冲区事件。

· 来自 sp_server_diagnostics 的系统组件结果。

· scheduler_monitor_system_health_ring_buffer_recorded 收集的实例运行状况。

· CLR 分配失败。

· 使用 connectivity_ring_buffer_recorded 时的连接错误。

· 使用 security_error_ring_buffer_recorded 时的安全错误。

其实我们用system_health会话最多的地方，就是查看、分析死锁信息。这也是为什么这篇文章要总结的原因。因为死锁是DBA经常遇到的问题之一，而system_health会话是捕获、分析死锁最佳工具，没有之一。

system_health会话配置

如果要确认system_health会话是否在启动时是否启动会话，可以使用下面脚本：

startup_state：0 (OFF)。服务器启动时不启动会话。

1 (ON)。服务器启动时启动事件会话。

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

在SQL Server 2008下碰到一个很奇怪的事情（只是一个特例，不是所有SQL Server 2008都有这种现象），system_health会话的create_time居然大于当前的getdate(). 似乎是个bug来着。

SELECT  s.name ,
        se.event_name,
        s.create_time
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会话的一些设置信息。如下脚本所示：

默认情况下，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会话的而设置。

    es.name, 
    esf.name, 
    esf.value 
    sys.server_event_session_fields AS esf
    sys.server_event_sessions AS es
    esf.event_session_id=es.event_session_id
WHERE es.startup_state=1
    AND (esf.name= 'filename' 
        OR esf.name ='max_file_size' 
        OR esf.name='max_rollover_files')
    es.name, 
    esf.name, 
    esf.value 
    sys.server_event_session_fields AS esf
    sys.server_event_sessions AS es
    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会话的的文件大小，以及文件保留个数。

ALTER EVENT SESSION [system_health] 
ON SERVER STATE = STOP
ALTER EVENT SESSION [system_health] 
ON SERVER DROP TARGET package0.event_file 
ALTER EVENT SESSION [system_health] 
ON SERVER ADD TARGET package0.event_file 
    (SET FILENAME=N'system_health.xel',--name of the session
    max_file_size=(24), --size of each file in MB
    max_rollover_files=(50)) --how many files you want to keep
ALTER EVENT SESSION [system_health] 
ON SERVER STATE = START

其实你也可以定制自己的扩展事件捕获死锁。这里不做展开介绍。在SQL Server 2008下面，由于system_health会话将事件保存在ring buffer target中，由于ring buffer target的大小限制以及sys.dm_xe_session_targets这个DMV的target_data列只能输出大约4MB的XML数据，这样有可能导致你看不到当前的死锁信息（Missing Events）或者有些旧事件被清除了。从而导致你无法查看某些时间段的死锁信息。所以有时候会定制一些扩展事件。

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耗尽时，每个类型中创建时间最早的事件从内存中被移除。

The ring buffer target briefly holds event data in memory. This target can manage events in one of two modes.

· The first mode is strict first-in first-out (FIFO), where the oldest event is discarded when all the memory allocated to the target is used. In this mode (the default), the occurrence_number option is set to 0.

· The second mode is per-event FIFO, where a specified number of events of each type is kept. In this mode, the oldest events of each type are discarded when all the memory allocated to the target is used. You can configure the occurrence_number option to specify the number of events of each type to keep.

我们可以使用下面脚本来查看ring buffer target中的死锁信息。

    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' )
      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
               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 )
ORDER BY [LastTranStarted];

注意：在SQL Server 2008中，system_health会话信息保存在ring buffer target中，会出现一些死锁信息不能及时查到的情况（后续可以查看），这个是因为一些限制缘故。摘抄“Why I hate the ring_buffer target in Extended Events”的解释如下，详情参考这篇文章。

This is by far the most common problem I have to explain about the ring_buffer target by email. Generally the question is phrased along the lines of:

“Hey Jonathan,I got the code below from one of your articles on SQL Server central and it is not working. The problem I have is that when I run the code, it doesn’t show any deadlock graphs even though I know one just occurred in the application. It seems like I only see older deadlocks in the system_health session, but never the most recent one. I’ve turned on Trace 1222 and get the information that way, so why doesn’t this work.”

The reality of the situation is that the events are actually there, you just can’t see them because of a limitation of the sys.dm_xe_session_targets DMV. The target_data column of this DMV can only output roughly 4MB of XML data. The information about the 4MB formated XML limitation of the DMV was explained by Bob Ward on the CSS SQL Server Engineers blog post You may not see the data you expect in Extended Event Ring Buffer Targets…. back in 2009. To demonstrate the effect of this limitation, lets look at the number of events contained in the ring_buffer target for the system_health event session on a SQL Server 2012 SP1+CU7 server that I have permission to share the information from using the following query.

如果system_health会话使用事件文件保存数据的话，可以使用下面脚本查询死锁信息：

DECLARE @path  NVARCHAR(260);
    TOP  1 @path=[path]
    SYS.dm_os_server_diagnostics_log_configurations
SET @path=@path +'system_health*.xel'
SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(),CURRENT_TIMESTAMP), T.execution_time_utc) AS event_time,
       T.dead_lock_xml
SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') AS dead_lock_xml,
       CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as execution_time_utc
FROM sys.fn_xe_file_target_read_file(@path, null, null, null)
WHERE object_name like 'xml_deadlock_report'
ORDER BY 1 DESC