I have demonstrated over the past couple of articles the basics involved with reading the

event_file target along with a more advanced technique.

In each of those articles I outlined some problems that could be encountered. Some of those pitfalls might include another DBA moving the location of the files and not documenting the change (the documentation still needs to be done but it takes a few extra moments to figure out the new location and can be frustrating) or potentially that the session is no longer active (this can be bothersome and cause a bit of hair loss). Both are legitimate concerns and can be solved, it just takes a little more planning up front to prevent the problems.

In this article, I am going to address some better practices for Reading Event_file targets attached to an XEvent Session. These better practices include how to retrieve the data file and directory dynamically regardless of the running state of the session. In addition to that, I will show a method to help parse the data more quickly.

Dynamically Read event_file Data

Similar to the prior article, I will take the basic example provided previously and use it as a starting point.

SELECT CONVERT(XML, t2.event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('C:DatabaseXEDemoSession*.xel',NULL,NULL,NULL) t2

Now that I have the basics in place, I need to find a different means to find the filepath based on the name of the session that does not require looking at just the running session metadata. For this, the filename is not as obvious as you may think. Logic may dictate that the data should be in the metadata for the session target, or sys.server_event_session_targets, but that isn’t the case. You may recall that I had mentioned that in a prior article in this series. The source of this data happens to be found in the sys.server_event_session_fields view. Since this is an EAV model, it also means that I have to more or less look for some value in the “name” filed that indicates it is the target file path. With that in mind, I can then create a query such as the following to get my first building block in place.

SELECT sesf.value
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'demosession'

The results of this method are pretty clear and simple. I have a file and path directly without the need to parse any XML as was necessary with the method shown that involves querying the running session metadata. Since the data is coming from an EAV model, the value column is defined as a sql_variant data type so I need to convert it. Additionally, I need to add a little trickery to my query to help it find the actual files on disk.

Notice in the results that the original filename is returned as the value. This does not include any of the additional information that is appended to each file in the target. Because of this, a quick REPLACE can be used to insert a wildcard into the filename. This is shown in the next example:

SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'demosession'
) cte1
CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2

This is now returning the same sort of results as should be expected for any data in the target for the session. All that is left to do now is tie that back into a query that will parse the XML from the target file(s).

/* deployed or running session */SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(event/data[@name="collect_database_name"]/value)[1]','bit') AS collect_database_name
,event_data.value('(event/data[@name="database_id"]/value)[1]','int') AS database_id
,event_data.value('(event/data[@name="object_id"]/value)[1]','int') AS object_id
,event_data.value('(event/data[@name="index_id"]/value)[1]','int') AS index_id
,event_data.value('(event/data[@name="job_id"]/value)[1]','int') AS job_id
,event_data.value('(event/data[@name="job_type"]/value)[1]','varchar(max)') AS job_type
,event_data.value('(event/data[@name="status"]/value)[1]','varchar(max)') AS status
,event_data.value('(event/data[@name="duration"]/value)[1]','int') AS duration
,event_data.value('(event/data[@name="retries"]/value)[1]','int') AS retries
,event_data.value('(event/data[@name="success"]/value)[1]','bit') AS success
,event_data.value('(event/data[@name="last_error"]/value)[1]','int') AS last_error
,event_data.value('(event/data[@name="count"]/value)[1]','int') AS EventCount
,event_data.value('(event/data[@name="statistics_list"]/value)[1]','varchar(max)') AS statistics_list
,event_data.value('(event/data[@name="database_name"]/value)[1]','varchar(max)') AS database_name
FROM ( SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'demosession'
) cte1
CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2
) AS evts ( event_data )
ORDER BY [TIMESTAMP];

Great! I now no longer need to know exactly the path for the session in order to query the data in it. In addition, it doesn’t matter if the session is running or just merely deployed. This adds the additional benefit that I can run the session to trap the data I hope to get, then stop the session when I think I may have it. Once done, I can evaluate the data and not have the extra overhead of the session running while I look into what has been captured.

This brings us to the next better practice to use when working with the session data. If there are a lot of events in the target to parse, then it can be painfully slow to retrieve the data to evaluate. There needs to be a means to doing it faster.

Faster Session Parsing

This is a really easy fix. The recommendation to help speed things along to read the session data is to first dump the data into some sort of table storage. That table can either be a temp table or a permanent staging table. The decision is up to you! For my example, I will just use a temp table.

This is how I would do it:

SELECT t2.event_data AS event_data
INTO #eventdata
FROM (SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'demosession'
) cte1
CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2
;

This will convert the event_data field into NVARCHAR in the temp table. This means I will need to CONVERT it back to XML in order to parse it. And then to get to that data for my parsing query, I just add it back in as shown in the next example:

SELECT CONVERT(XML, t2.event_data) AS event_data
INTO #xmlpreprocess
FROM (SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),'.xel','*.xel') AS targetvalue
FROM sys.server_event_sessions ses
INNER JOIN sys.server_event_session_fields sesf
ON ses.event_session_id = sesf.event_session_id
WHERE sesf.name = 'filename'
AND ses.name = 'demosession'
) cte1
CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,NULL, NULL, NULL) t2
;
--EXECUTE sys.sp_help 'tempdb.dbo.#xmlpreprocess'
SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(event/data[@name="collect_database_name"]/value)[1]','bit') AS collect_database_name
,event_data.value('(event/data[@name="database_id"]/value)[1]','int') AS database_id
,event_data.value('(event/data[@name="object_id"]/value)[1]','int') AS object_id
,event_data.value('(event/data[@name="index_id"]/value)[1]','int') AS index_id
,event_data.value('(event/data[@name="job_id"]/value)[1]','int') AS job_id
,event_data.value('(event/data[@name="job_type"]/value)[1]','varchar(max)') AS job_type
,event_data.value('(event/data[@name="status"]/value)[1]','varchar(max)') AS status
,event_data.value('(event/data[@name="duration"]/value)[1]','int') AS duration
,event_data.value('(event/data[@name="retries"]/value)[1]','int') AS retries
,event_data.value('(event/data[@name="success"]/value)[1]','bit') AS success
,event_data.value('(event/data[@name="last_error"]/value)[1]','int') AS last_error
,event_data.value('(event/data[@name="count"]/value)[1]','int') AS EventCount
,event_data.value('(event/data[@name="statistics_list"]/value)[1]','varchar(max)') AS statistics_list
,event_data.value('(event/data[@name="database_name"]/value)[1]','varchar(max)') AS database_name
FROM #xmlpreprocess AS evts
ORDER BY [TIMESTAMP];
DROP TABLE #xmlpreprocess;

I have left the sp_help in the example as a quick means to go back and verify that the event_data is truly NVARCHAR, should you choose to do so.

Adding this little step of dumping the data into a table first helps improve the queries to parse the event data by a factor of 3 and sometimes more.

I have shown in this article how to parse session data more efficiently. I have also shown how to ensure the correct file and path can be used every time to get the session data by using just the session name. These tips will help ensure a more pleasant experience when trying to parse the event data from a session.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.