How to parse sys.fn_xe_file_target_read_file faster

26 March,2021 by Jack Vamvas

Question: I've captured extended events messages into an xel file, and trying to read from the file with this query using the Extended Events function  sys.fn_xe_file_target_read_file. I've discovered it parses the file very slowly. Is there a way of speeding up the parsing performance?

 

SELECT
    [XML Data],
    [XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME')             AS [Timestamp],
    [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)')        AS [Database],
    [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')                AS [Message],
    [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [Statement]
	
FROM
    (SELECT
        OBJECT_NAME              AS [Event], 
        CONVERT(XML, event_data) AS [XML Data]
    FROM
        sys.fn_xe_file_target_read_file
    ('C:\tmp\CaptureErrors_0_1.xel',NULL,NULL,NULL)) as FailedQueries

Answer: My recommendation is to dump the data into either a temp table or permanent table. Once you've dumped the data , it's much faster to process the data. Here is an example of creating a table , dumping the .xel file into the table and then searching the table.

 

create table eventdata_tbl
(id int identity(1,1) PRIMARY KEY,
xml_data xml,
ts nvarchar(max),
db  nvarchar(max),
message_txt nvarchar(max),
sql_text nvarchar(max)
)

INSERT INTO eventdata_tbl
SELECT
    [XML Data],
    [XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME')             AS [Timestamp],
    [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)')        AS [Database],
    [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')                AS [Message],
    [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')             AS [Statement]
	
FROM
    (SELECT
        OBJECT_NAME              AS [Event], 
        CONVERT(XML, event_data) AS [XML Data]
    FROM
        sys.fn_xe_file_target_read_file
    ('C:\tmp\CaptureErrors.xel',NULL,NULL,NULL)) as FailedQueries
	                                                                               
GO

select * from eventdata_tbl

Read more on Extended Events

How to capture query errors using SQL Server Extended Events ...

How to Audit user DML with SQL Server Extended Events (SQL ...

How to read and parse the Microsoft SQL Server extended event log ...

How to capture query errors using SQL Server Extended Events


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to parse sys.fn_xe_file_target_read_file faster


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer