Viewing Deployed XEvent Metadata

Comments: 6 Comments
Published on: September 2, 2015

Today will be the first of a few introductory level posts about Extended Events.

When dealing with Extended Events, there are a few ways to take a look some of the metadata. Not all metadata is created equal when dealing with Extended Events. Some of the metadata is pertinent to a running XEvent session and some is pertinent to a deployed session.

If you have a session that has been configured/deployed to an instance of SQL Server, you will want to look at the Catalog Views – especially if that session is not running. Today, I will take a quick look at the catalog views. Yes – quick!

Catalog Views

First, I want to cover a quick query that will reveal what catalog views are available for your use. The query is very simple.

Straight forward, catalog views for Extended Events have a very common naming pattern – server_event_sessions%. The rest of the predicate is just for eye candy.

Among the list returned by that query we will see a list of results such as this:

Use the following catalog views to obtain the metadata that is created when you create an event session.

Name Description
sys.server_event_sessions Lists all editable, deployed event sessions.
sys.server_event_session_actions List of actions on each event of an event session.
sys.server_event_session_events List of each event in an event session.
sys.server_event_session_fields List of customizable columns that were set on events and targets.
sys.server_event_session_targets List of event targets for an event session.

Beyond just a list of the Catalog views, I find it useful to combine some of these to garner the information that would be useful in querying data from the session or even to possibly rebuild the event session if needed.

One may want to figure out where the data for an event session is being stored. The first inclination may be to look at the server_event_session_targets catalog view. Doing that could cause a little frustration since you will only see some binary data and the type of target attached to the session.

But, playing with the views a little more and becoming a little more familiar with the data presented by the views, you may notice that the server_event_session_fields suddenly becomes more attractive. Why? Well, because it contains the filepaths that would be necessary to query session data – if the session is deployed to a file target. Suddenly, hope is not lost.

To get that data, one would need to write a query like this:

This demonstrates one of the more frustrating (not by far the most frustrating part though) things about dealing with extended events. That is the entity attribute value model employed to store metadata. This is nothing new within SQL Server (e.g. agent jobs, schedules, or even sysobjvalues – internally). Sadly that doesn’t make dealing with the metadata terribly easy – but knowing can make it more manageable.

So, my recommendation here is to play around a bit and start to get to know the catalog views as they pertain to extended event metadata.

Stay tuned for more posts like this. As was mentioned, this is the first in what will be a long series on Extended Events.

6 Comments - Leave a comment
  1. […] Viewing Deployed XEvent Metadata September 2, 2015 […]

  2. Marios Philippopoulos says:

    Hi,
    Thank you for this series of articles.
    I will certainly be following it.

    Is there a way to find historical information on extended-event sessions, such as execution times, durations etc.?
    I have not been able to figure this out.

    Thanks again,
    Marios Philippopoulos

    • Jason Brimhall says:

      If you are running the session and storing the data. If you don’t have the session running, you will not have access to the historical data for that time frame. If you want to ensure the data is persisted for a really long time, I recommend storing the data into a table. Use one of any number of methods to take the data from the event target, parse it and dump it into a table for long term storage.

  3. […] Viewing Deployed XEvent Metadata September 2, 2015 […]

  4. […] Viewing Deployed XEvent Metadata September 2, 2015 […]

  5. […] Viewing Deployed XEvent Metadata September 2, 2015 […]

Leave a comment

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










Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Monday, April 24, 2017