Session Event Metadata

Comments: 2 Comments
Published on: September 29, 2015

There is a wonderful amount of metadata available to be perused in Extended Events. Part of the trick is to know where and how to find it. I started to dive into the investigation of this metadata with the exploration of the Deployed Extended Event Session Settings discussed here. And that article is just one of many in the series that can be explored from here.

In this article, I am going to continue diving into the metadata of deployed Extended Event Sessions. While these articles are designed to build upon one another, the connection may not be entirely evident. Hopefully, that will be cleared up as I progress through the series. With that said, the topic at hand is to explore the metadata of the events deployed with an Extended Event Session.

Session – Event Metadata

Exploring the metadata for Events tied to a deployed session is actually rather easy. It is also strikingly similar to how one would explore the settings metadata for the deployed session. To explore this metadata, I will be bringing back a common event session script:

And in similar fashion as the previous article, here is the script again with just the focal point of this article:

Here, one can see that I have the event name, along with a customizable data point being set, and finally the predicate. These are all specific to the event metadata and are items to keep close at hand as I demonstrate how to explore the metadata for the events deployed to a session.

With that said, how does one explore the metadata tied to this event? One simply needs to query sys.server_event_session_events. That can be really simple in form, or it can be a bit more complex. First, I will explore the event metadata in a moderately basic fashion.

Notice the connection in this script between sys.server_event_session_events and sys.server_event_sessions. I joined the two views in order to filter the results down to just the “demosession” session.

In this view, there will be one row for each event attached to the session. Each event in the session will also include the package name, module and predicate for that specific event. This helps to reinforce that the Extended Events engine allows a highly configurable filtration system for events occurring within the instance.

Recall from the discussions on predicates that a predicate is limited to 3000 characters? Looking at this view, it becomes apparent, at least in part, why that limit exists. The max length for the predicate column is NVARCHAR(3000) – or 3000 characters.

Also of note here is the predicate_xml column. A quick intro to the predicate xml format was given in the same article where the limit was introduced. Knowing that this is XML, let’s enhance the query a little bit. In addition to an enhancement for the XML, I will also add the exposure of the package metadata (remember – building blocks).

This will return a well formatted (and clickable) XML column for the predicate_xml. In addition, I can see which dll exposes the package that contains the event in question. Being able to look at a well formatted predicate in XML is very handy when exploring this metadata and when trying to get a better understanding of how the event payload may or may not be trapped (remember the short-circuit topic?).

With a little imagination, one could evolve to a query such as this next one to look at the event information in a bit more detail.

With this last query, I have exposed the available payload for the event tied to the “demosession” session. In addition, I can also see which of these columns in the payload is “customizable” and which is of the “data” type. This is the sort of foundation that is useful when looking to build administrative type scripts for the toolbox. One such example of an useful script can be found in this article on shredding session XML.

Despite the seemingly simple nature of the catalog view, this is a pretty valuable view. This view exposes just enough data to help tie a few building blocks together as well as provide the means to create usable administrative scripts down the road. In this article, I exposed the predicate as well as a means to see the available payload attached to an event that has been deployed in a session. This last piece left one little piece yet to be discussed. That little piece involves the “customizable” type for the deployed session event payload. I will cover this in more detail in an upcoming article.

In case you have missed any articles in this series, here is the recap!

2 Comments - Leave a comment
  1. […] Session Event Metadata September 29, 2015 […]

  2. […] Session Event Metadata September 29, 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 Tuesday, December 12, 2017