[SOLVED] Reading system_health*.xel files efficiently

Reading system_health*.xel files efficiently

I want to gather granular details of an SQL deadlock. I am trying to use this query for that. Since I have to run this in a production environment, I am worried if it will cause an overhead. Is there any filters that i can apply such that the dataset be limited to today or yesterday?

SELECT CONVERT(XML, event_data) DeadLockXMLData
    ,ROW_NUMBER() OVER (
        ORDER BY Object_name
        ) DeadLockNumber
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME = 'xml_deadlock_report'

Solution

I think i can use this for the need. from documentation

SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE timestamp_utc > dateadd(day, -1, GETUTCDATE())

PS: this timestamp_utc Applies to: SQL Server 2017 (14.x) and later and Azure SQL Database.