SET Operations and Metadata

Having covered the core concepts of deployed session metadata (events, actions, targets, predicates), there is one more topic to cover. This is more of a subset of those concepts in that this applies to more than one of the core concepts, but it isn’t really a stand-alone topic. This is the sub-topic of SET operations. This also happens to be the topic I was referring to when I covered the metadata of session events and targets.

SET operations in extended events are the extra configurations that can be made to various components. As an example, a target can be configured with certain properties via the use of a SET operation when creating the Extended Event Session. The best way to see this is by seeing it in action.

SET Operations and Metadata

It is time, once again, to break out that tried and true example script that I have been using throughout the past several articles.

If I follow the same pattern as has been done in previous articles, I could eliminate everything from the script that is not a set operation. Instead, I will just highlight those SET operations with the following image.


Keeping with the same color-coding that has been used in the previous articles, I have added just a few highlights. Highlighted in purple, are all of the SET operations for this particular session creation script. In the case of the Targets, these set operations are properties of the Target. In the case of the Event SET operation, this happens to be a “customizable” type within the Event payload.

Now that we know what these SET operations can be, and where they might exist, how does one access this information? It was seen in the article about the deployed Event metadata and also the article about deployed Target metadata, that this data is not visible within those views. This is where the entity attribute value data model comes back into play within Extended Events. To expose this metadata, one needs to query the sys.server_event_session_fields system catalog view.

Querying the sys.server_event_session_fields view directly will produce some interesting information. While interesting, it is less than complete. Sure, if you query the view, you can see various attributes and the value of those attributes. Unfortunately, the picture is just not complete.

For instance, if you query this view, you will see that there is a name, value, session id, and even an object id. The object id is not entirely descriptive as to what exactly it is (and neither is the documentation). However, one could make the quick presumption based on some of the attribute names and arrive at a conclusion that the object id maps to the id field of the other metadata views (e.g. event_id and target_id). Working with this information, a more clear picture can be painted via the following query.

This query joins back to the event metadata and target metadata based on the object_id. If there is a SET operation for the Event or the Target, then this query will return those results. You may be wondering why I am only looking at the sys.server_event_session_target and _event views. As it happens, the core component of Actions does not have a set operation. As for the Predicate core component, recall that the Predicate is stored as a property of the Event.

Looking at the results of that query for the “demosession” session, I would see this:


Comparing these results to the script, I can easily see how the properties start to map and see that the presumptions that I made were accurate. Now I can see all of the critical information that is affiliated to the creation of a Target for an Event Session.

An important note about one value, in particular, needs to be made. The filename property only has the name of the file in this particular case. If I had specified more than just a filename, and decided to include a specific filepath for instance, then the path and filename would be the value for this property. But since I only provided a name for the file without a path at session creation, the file was created in the default path and the engine will look in that default path automatically if a path is not created (when looking to query the file for example).

To this point, I have now covered every facet of how to retrieve the metadata for a deployed Session. I have demonstrated how to retrieve everything from the Target to the Event to the Predicate and all the way down to the SET operations and specific Session level settings. With all of this information, I will show what can be done with it in the next article.

With all that has been covered (and the length of this series) on the topic of Extended Events, it is easy to have missed an article in the series, or to even need a quick recap of one of the topics. If this is the case, then feel free to peruse the table of contents – here.

Session Target Metadata

target_metadataWith the ability to find the metadata for deployed session, events and actions firmly under the belt, the natural progression would lead one to find the metadata for the target(s) attached to a session.

Exploring the metadata for the core components of Extended Events Sessions can be an extremely fruitful undertaking. In the case of exploring the Event metadata for deployed sessions, one can also quickly discover the predicate for that event.

In the case of the final core component, Targets, the exploration into the metadata can be a little awkward. At least at first.

Session Target Metadata

Once again, I will refer immediately back to the example code I have used throughout these dives into the metadata internals.

And now, with a little less noise and just a focus on the targets of the session.

In this condensed version of the code, all that is present (of interest) is the code necessary to create two targets for this session as it gets deployed. Quite similar to how one can explore the metadata of the session, events, and actions, one merely needs to query the catalog view for the targets – sys.server_event_session_targets. Or so it would seem. Let’s start with the base query once again.

For the “demosession” session that has been deployed, I will receive the following results:


Take note of the name column from sys.server_event_session_targets. In my query, I have renamed it to Targettype. If this is compared back to the original script, it becomes apparent that the target type or name of the target type is what is used in this particular view. These results represent essentially the entirety of the catalog view (minus the event_session_id column which I used to join back to sys.server_event_sessions in order to filter by session name).

Unlike the metadata for the session, events and actions, the target metadata view is less useful. That is, by itself. Sure I can find out the types of targets attached to the session. But this view directly does not give me the pertinent information such as the file name when a file target is used. With that said, the information is available and I will be covering that in the next article.

As for further use out of this particular view, there is still some metadata to be gained. Let’s dive just a bit more.

For now, this is still pretty basic information. As I said just a moment ago, the more fruitful information is not readily available direct from this view. The exploration of reaching that information will be revealed in the next article.

It is important to discuss this particular view to try and help prevent a bit of frustration. I would personally think that the view should expose the pertinent metadata for the session. This is one case where extra steps need to be taken and an exploration into a different view will be required – a view that does not necessarily equate to Target.

In this short tutorial, I covered a quick means to view the target types that are deployed for a specific session. This is just one of a long series of articles on Extended Events. Explore the other articles here.

Session Action Metadata

Comments: 1 Comment
Published on: September 30, 2015

hurdleIn a previous article, I introduced the core concept of Actions. In that article, I explained that an Action is a means to attach additional data from the stream to the event payload.

Recently, I have been covering various aspects of how to build an event session as well as how to investigate metadata related to any session that might be deployed to the server. The articles discussing these aspects can be found in the recap (Table of Contents) of the series.

In this segment, I will continue to build on the core concepts previously discussed. Knowing how to access the deployed session metadata and the event metadata attached to that session, I will now begin the dive into how to find the same relevant data for any actions that may be deployed.

Recall that an Action is tied directly to an Event. This means that each Event in the session can have one or more, or no actions attached to it. This contributes to the configurability of Extended Event Sessions.

Session Action Metadata

Resorting back to a common theme, I will re-introduce the example script used over the course of the past several articles.

And now, that same script, with just the pertinent pieces to see the Actions.

Looking at this example with just the necessary pieces for the Actions, it should stand out that I did not exclude the addition of the Event to the session. This is to help reinforce that an action has to be tied to an Event in order to be deployed to a session. In this example, I have assigned four actions to the auto_stats event.

If I have the script to build this session, that is great. If I have a GUI to access the Session data, great. If I have neither of those at my disposal, how do I get to the metadata for these deployed Actions? In response to that need, there is a catalog view that exposes this metadata – sys.server_event_session_actions. And to get at the data, a query such as the following will do the trick at the base level:

I have joined this view to the sys.server_event_sessions catalog view in order to filter this down to the specific deployed session I want to investigate. In the results, I can see which actions have been deployed as well as an id that correlates to the Event for which the Action has been deployed. In addition, like was seen with the sys.server_event_session_events view, the package that exposes the specific action is listed with the corresponding module guid.

This is a good basic query to begin the discovery process into deployed Action metadata. As I showed with the deployed Event metadata, there is more to discover. Let’s step it up a bit to find more about these deployed Actions.

The results for this example query would be as follows:



While some of this is more informational at first glance, it could all be somewhat useful at one time or another. In this query, I chose to return results for the data type of each of the actions in addition to the dll and package that happen to be the source of the action. In addition, I can also see what the deployed Action is supposed to do (e.g. the description of the Action).

In the same fashion as the deployed Events, queries such as I just demonstrated can be used to create tools for the DBA toolbox.

Stay tuned for more in this series and the world of Extended Events. If you have missed any of the tutorials and articles, you can catch up here.

Session Event Metadata

Comments: No 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!

Deployed Session Settings

Comments: 1 Comment
Published on: September 28, 2015

You may recall that I wrote about how to look at the deployed session metadata. Today, I will be talking about deployed sessions again. And it may even be considered metadata as well. This time around, the metadata I will be discussing is a little bit different but will build on that first article. For a refresher on the previous article, you can read it here.

From that first article on session metadata, I listed some views that would be relevant to deployed session metadata. For today, my focus will be on the sys.server_event_sessions view. Beyond building upon the previously mentioned article, I also want to build on a few things that have been discussed over the past couple of articles – particularly the articles demonstrating how to create an event session via script and GUI. You can get links to those articles as well as all articles in the series by checking out the Table of Contents.

Deployed Session

To get things started, I will bring back a script that was recently used to build a session.

You may recall this example session, only this time I have not color coded any of the components. The focus of this article will be around the session specifically, saving the components for future articles. This means that if I were to strip the components out of the script, I would see a script such as the following:

That significantly simplifies things. And that script brings into focus the settings and metadata that I will be discussing.

Session Settings

Keep that script close at hand, I will be referencing it more. Now it is time to start looking into the catalog view sys.server_event_session. This view is the source of the definition of any event sessions that have been deployed. If I have already deployed the event session from the prior script, a quick query to the view, for that session name, is in order.

I am going to break this query up into two for ease in viewing the results.

And the results:


Between the script to query the view and the results, it should appear evident that this catalog view stores some key configuration settings for the event session. Or as was previously stated – the session definition. The highlighted data in the image relate directly back to the script used to create the session. As I mentioned in the article on creating a session via the GUI, the first requirement of a session is a session name. Here, we see that the session name is stored and subsequently exposed via this view.

When creating a session via script, if there is a setting specific to the session that you can’t recall the name of exactly, querying this particular view can help remedy that. One would just use the column names and use the columns whose names do not end in _desc where one of with and one without may exist (e.g. event_retention_mode vs. event_retention_mode_desc).

Important notes here with this data are:

  1. MAX_DISPATCH_LATENCY is stored as milliseconds but seconds can be supplied via GUI or TSQL Script.
  2. MAX_EVENT_SIZE is stored as KB yet MB (for example) can be supplied and it will be converted automatically.
  3. MAX_MEMORY is also stored as KB with the same note as MAX_EVENT_SIZE. The default value here is 4 MB.
  5. MEMORY_PARTITION_MODE accepts NONE, PER_CPU, and PER_NODE as values where “None” is the default.
  6. The startup_state in my script is on and in the view it reports as off. This is because I altered that setting after the fact.

So what do these settings mean? Very quickly, here is the run-down.

  1. MAX_DISPATCH_LATENCY – The max time in milliseconds that an event will stay in buffers before being sent on to the target.
  2. MAX_EVENT_SIZE – Memory set aside for events too big to fit in session buffers.
  3. MAX_MEMORY – Memory allocated for event buffering.
  4. EVENT_RETENTION_MODE – How many events can be afforded to be lost.
  5. ALLOW_SINGLE_EVENT_LOSS – Single event is permitted to be lost when all event buffers are full.
  6. ALLOW_MULTIPLE_EVENT_LOSS – Allows potentially large numbers of events to be lost from the session.
  7. NO_EVENT_LOSS – Not recommended. The name defines it.
  8. MEMORY_PARTITION_MODE – Location in memory where event buffers are created.
  9. NONE – Single set of buffers created within SQL Server
  10. PER_CPU – A buffer is created for each CPU
  11. PER_NODE – Buffers created for each NUMA node.
  12. TRACK_CAUSALITY – Enables the ability to track related events on different connections. By default this is disabled.
  13. STARTUP_STATE – Enables the session to be started when the server is started. The default setting is off.

I have just shown how to view deployed session settings and then briefly described these settings that are available for an Event Session. Having these settings exposed can be a very handy tool for the Database Administrator. I will be demonstrating how useful this can be in an upcoming article.

«page 1 of 96

October 2015
« Sep    


Welcome , today is Sunday, October 4, 2015