SQL Server: The Broken fn_xe_file_target_read_file Function

Extended Eventually

SQL Server has had the fn_xe_file_target_read_file function for a while, but starting with SQL Server 2017, a column called timestamp_utc was added to the output.

Somewhat generally, it would be easier to filter event data out using this column… if it worked correctly. The alternative is to interrogate the underlying extended event XML timestamp data.

That’s… not fun.

But if you write your query like this:

SELECT
    xml.wait_info
FROM
(
SELECT
    wait_info =
        TRY_CAST(fx.event_data AS xml)
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx
WHERE fx.object_name = N'wait_info'
AND   fx.timestamp_utc >= DATEADD(DAY, -1, SYSUTCDATETIME())
) AS xml
CROSS APPLY xml.wait_info.nodes('/event') AS e(x);

It will return no rows. Not ever. But at least the predicate is pushed down to a reasonable place: when you touch the file.

But if you write your query like this, it’ll work, at a cost:

SELECT
    xml.wait_info
FROM
(
SELECT
    wait_info =
        TRY_CAST(fx.event_data AS xml)
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx
WHERE fx.object_name = N'wait_info'
AND   CONVERT(datetime2(7), fx.timestamp_utc) >= DATEADD(DAY, -1, SYSUTCDATETIME())
) AS xml
CROSS APPLY xml.wait_info.nodes('/event') AS e(x);

The cost is an additional filter operator in the plan, which causes the entire file to be read and then filtered.

For large on-disk XML storage files, that can be really painful to deal with. It’s especially ugly because there’s no parallel read for these files.

There’s an open issue about this here that you should go and upvote.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.