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;
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.
It looks like your XML path specification is for a XE ring buffer target instead of file target. Try the query below.
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.