Better Practices for Reading Event_file Targets

I have demonstrated over the past couple of articles the basics involved with reading the
event_file target along with a more advanced technique.

db_xesession_xmlIn 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.

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.

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:

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).

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:

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:

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.

 

4 Comments - Leave a comment
  1. […] Better Practices for Reading Event_file Targets October 15, 2015 […]

  2. […] Better Practices for Reading Event_file Targets October 15, 2015 […]

  3. jeff quinn says:

    I am having problems using sql database (azure) to find the actual file associated with the last session completed. I have looked through all of the tables you mention and don’t see how to locate it? Here is the script I ran but no results are returned.

    SELECT CONVERT(XML, t2.event_data) AS event_data
    FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),’.xel’,’*.xel’) AS targetvalue
    FROM sys.database_event_sessions ses
    INNER JOIN sys.database_event_session_fields sesf
    ON ses.event_session_id = sesf.event_session_id
    WHERE sesf.name = ‘filename’
    AND ses.name = ‘RPC and SP Completed’
    ) cte1
    CROSS APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue, NULL, NULL, NULL) t2

    • Jason Brimhall says:

      Sorry for such a late reply. I have been offline for a bit. Let me look into that and post a follow-up. Can you post the session definition that you created just to do an apples to apples comparison?

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
October 2015
M T W T F S S
« Sep   Dec »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Monday, March 27, 2017