如何在生产环境高效读取system_health*.xel并过滤SQL死锁数据

当然可以通过添加时间过滤条件缩小数据集范围，减少查询对生产环境的性能影响。system_health扩展事件文件中的每个死锁事件都包含UTC格式的时间戳，我们可以从event_data的XML结构中提取该时间戳，转换为服务器本地时间后进行过滤。

过滤今日的死锁事件 #

SELECT CONVERT(XML, event_data) AS DeadLockXMLData
    ,ROW_NUMBER() OVER (ORDER BY Object_name) AS DeadLockNumber
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME = 'xml_deadlock_report'
    -- 将UTC时间戳转换为本地时间，过滤今日数据
    AND CONVERT(DATETIME2, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, 
        XML_EVENT_DATA.value('(/event/@timestamp)[1]', 'DATETIME2')), 
        DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) >= CAST(GETDATE() AS DATE)

过滤昨日的死锁事件 #

SELECT CONVERT(XML, event_data) AS DeadLockXMLData
    ,ROW_NUMBER() OVER (ORDER BY Object_name) AS DeadLockNumber
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME = 'xml_deadlock_report'
    -- 将UTC时间戳转换为本地时间，过滤昨日数据
    AND CONVERT(DATETIME2, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, 
        XML_EVENT_DATA.value('(/event/@timestamp)[1]', 'DATETIME2')), 
        DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) 
    BETWEEN DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) 
        AND DATEADD(MILLISECOND, -1, CAST(GETDATE() AS DATE))

关键说明 #

• 扩展事件的时间戳默认是UTC时间，使用SWITCHOFFSET转换为服务器本地时区，避免因时区差异导致漏查或错查数据。
• 这种过滤方式会先读取匹配文件名的文件，再基于时间戳筛选事件，相比全量解析所有事件，能显著降低CPU和IO开销。
• 如果生产环境的system_health文件数量极大，还可以直接指定包含目标日期的具体文件名（比如system_health_0_132xxxxxx.xel），进一步缩小读取范围。

内容的提问来源于stack exchange，提问作者Tech with Thiru