SQL Server扩展事件system_health会话总结

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) 。   服务器启动时启动事件会话。

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'

在 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 会话的而设置。

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 (esf.name= 'filename' 
        OR esf.name ='max_file_size' 
        OR esf.name='max_rollover_files')
 
或
 
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 会话的的文件大小，以及文件保留个数。

ALTER EVENT SESSION [system_health] 
ON SERVER STATE = STOP
GO
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
GO
ALTER EVENT SESSION [system_health] 
ON SERVER STATE = START
GO

其实你也可以定制自己的扩展事件捕获死锁。这里不做展开介绍。 在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 中的死锁信息。

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];

注意：在 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);
 
SELECT 
    TOP  1 @path=[path]
FROM
    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
FROM
(
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'
) T
ORDER BY 1 DESC
GO

注意：这个脚本可能不能满足所有的情形，例如，你将扩展事件的文件没有放置在默认位置。放置在其他地方 .... 等等。

参考资料：

https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/

https://docs.microsoft.com/zh-cn/sql/relational-databases/extended-events/use-the-system-health-session?view=sql-server-2017