sql-serverdatabase-administration

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 cast(timestamp_utc as datetime2(7)) > dateadd(day, -1, GETUTCDATE())