扩展事件中的死锁图未显示 [英] Deadlock graph from Extended Events not showing
问题描述
我有一个来自Sql Server 2017 Query Performance Tuning"(一本书)的查询.这本书的代码可以在这里找到:https://github.com/Apress/sql-server-2017-query-perf-tuning
I have this query which is from "Sql Server 2017 Query Performance Tuning"(a book). Code for the book can be found here: https://github.com/Apress/sql-server-2017-query-perf-tuning
DECLARE @path NVARCHAR(260)
--to retrieve the local path of system_health files
SELECT @path = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @path = @path + N'system_health_*';
WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
FROM sys.fn_xe_file_target_read_file(@path,
NULL,
NULL,
NULL) AS fx )
SELECT dl.deadlockgraph
FROM
( SELECT dl.query('.') AS deadlockgraph
FROM fxd
CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl) ) AS dl;
它应该从扩展事件中捕获死锁图.我刚刚按照这个示例生成了一个死锁(如何在 SQL Server 上模拟死锁?).
It is supposed to capture the deadlock graph from Extended Events. I just generated a deadlock following this example (How to simulate DEADLOCK on SQL Server?).
但是当我运行查询时,我得到一个空的结果集.
But when I run the query I get an empty result set.
推荐答案
看起来您的 XML 路径规范是针对 XE 环形缓冲区目标而不是文件目标.试试下面的查询.
It looks like your XML path specification is for a XE ring buffer target instead of file target. Try the query below.
--Get xml_deadlock_report events from system_health session file target
WITH
--get trace folder path and append session name with wildcard (assumes base file name is same as session name)
all_trace_files AS (
SELECT path + '\system_health*.xel' AS FileNamePattern
FROM sys.dm_os_server_diagnostics_log_configurations
)
--get xml_deadlock_report events from all system_health trace files
, deadlock_reports AS (
SELECT CAST(event_data AS xml) AS deadlock_report_xml
FROM all_trace_files
CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS trace_records
WHERE trace_records.object_name like 'xml_deadlock_report'
)
SELECT
deadlock_report_xml.value('(/event/@timestamp)[1]', 'datetime2') AS UtcTimestamp
, deadlock_report_xml AS DeadlockReportXml
FROM deadlock_reports;
要在 SSMS 中查看图形死锁报告,请从 XML 文档的开始/结束处删除开始/结束 event/data/value
标记,并将其另存为扩展名为.xdl"的文件;.然后在SSMS中打开文件查看死锁图.
To see the graphical deadlock report in SSMS, remove the opening/closing event/data/value
tags from the start/end of the XML document and save as a file with extension ".xdl". Then open the file in SSMS to see the deadlock graph.
这篇关于扩展事件中的死锁图未显示的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!