Extended Event Management Power Tools

Comments: No Comments
Published on: October 6, 2015

So far in this series I have introduced a couple of tools that are suitable for helping with the management of Extended Events. The tools introduced so far are the GUI and TSQL scripts. While the GUI is suitable, using a script really is a far better means of managing Extended Events.

That said, these are not the only tools that could be in the shed for helping in the posh_DBmaintenance and management of Extended Events. One really cool tool that can be added is PowerShell. Isn’t that just amazing?

PowerShell can pack a pretty big punch when dealing with Extended Events. With using PowerShell to manage Extended Events, there could be a bit of a learning curve, and it may not be the right tool all the time. I will cover some uses of PowerShell with XEvents over the next handful of articles.

Management Tools

Since I will be discussing the use of PowerShell to help manage Extended Events, a working knowledge of PowerShell would be somewhat advantageous. If PowerShell is a new endeavor, then I recommend checking out a few tutorials prior to proceeding. Here are a couple of options: Microsoft PowerShell Introduction, Month of Posh by Wayne Seffield, or this Month of SQLPS by Mike Fal (as of publication of this article, this series is ongoing). For my demos, I will be using the Windows PowerShell ISE. Feel free to use whichever tool you feel most comfortable, whether it be an ISE or direct to PowerShell.exe.

After spinning up my ISE, I am going to dive straight into SQLPS.


The first thing I need to do is to change the security policy so I can load the SQLPS. After loading SQLPS, I then revert the security policy back to what it was prior to the change. Here is that code snippet:

As shown in the previous image, I now have SQLPS loaded. Notice the directory change after the module load, going from C:\Windows\System32> to SQLSERVER:\>. With SQLPS loaded, I can start exploring a little bit.


Part of the exploration technique is to figure out what is available at each level. In this case, I use dir to figure out what is available to me as I progress through the structures. After the first dir from the SQLSERVER:\ , I can see the possible paths to follow and that one of them is XEvent. That is the path (for the default instance) that I want to follow, and I did as shown with this particular script.

Now, after I run another dir, I can see the beginnings of a wealth of information. I will get the names of all Extended Event Sessions, if the session is started and when that session was started. This is pretty good news. Seeing the list of sessions, I will pick a session and explore further.

Using the Get-Member method against the object I created into which I attached the session of interest, I can peruse the options available to work with the session.


Unlike doing this from within TSQL where views are used to expose the metadata, PoSH takes advantage of Methods and Properties that will permit the perusal of the various components of a session. To explore a little further, I can take advantage of the Metadata property.


Because of the way Extended Events is architected, there is some consistency between the way investigating Extended Events via PoSH vs. TSQL. Here, attached as an object to the Session, there are the Events and Targets objects. This also tells me that I can browse from within the session object down through the Events and the Targets.

To investigate the properties, like I did in the article on exploring session metadata, I can do so in PoSH by doing something like this (bear in mind, that these are scriptlets designed to work with prior examples):

And this would show me the following output:


These properties should be rather familiar. These are the same settings (the ones in grey outline) that are exposed via the sys.server_event_sessions catalog view. The variable here being that the StartTime property is not one of the properties in that view. Rather, this particular information can be handy and is easily accessed via PowerShell. The ID on the other hand, though not a setting that can be configured, is exposed via that view and is the same ID whether seen through the view or through PowerShell (just as are the properties outlined in grey).

There is a lot of information that is exposed through PowerShell for Extended Events. I have only begun to tap into the available information as I have begun to introduce this tool. I have just shown how to expose the various session settings as was done previously via TSQL in the aforementioned article.

This is just another quick tutorial in the series about Extended Events. Feel free to peruse the growing recap – here.

Backup Deployed Sessions

There is a wealth of information within Extended Events. Throughout this series, I have been working on exposing that wealth of information. A good bit of this information happens to be the metadata for Extended Events, and another bit of this information is tied craneto the metadata for deployed Sessions. To dive into what I have done so far with this plenitude of information, you can review the series here.

What is the point in covering all of this information? I like to call it building blocks. Take a concept little by little and build upon it, while working toward a more comprehensive understanding as well as to hopefully be able to use it for a bigger product.

Having just completed several segments on the metadata for deployed sessions, it is time to bring all of that information together and see how it can be used.

Bringing it Together

Having a solid foundation and understanding of what the metadata is and where it is really helps in creating scripts that can be useful in the day to day management of deployed Extended Event Sessions. For me, the ability to recreate a deployed session, without the use of the GUI is pretty essential. Contributing to the need to be able to do this via script and not a GUI is that (without a third party product) there is no GUI in SQL Server 2008.

To demonstrate how to create a script to serve as a backup of a deployed session, I will first return to the “demosession” script that I have used throughout this series.

And if I color-code this script as I have done previously:


In this image, I have separated out the major components by color. The segments that pertain to the session creation and the settings for that session are in grey. Then the target information is in red and so on. I added my notes (to help indicate where the components can be found in metadata) and colored them purple. The wrinkle here is in the SET operations since that is a component that crosses boundaries of the core concepts.

Taking this breakdown, and a serious desire to be able to recreate any session that has been deployed (without the GOOEY), and I have been able to create the following script that will do just that – backup deployed sessions.

I have tested this script back through SQL Server 2008 and up through SQL Server 2014. The script has been tested against complex sessions as well as simple sessions (e.g. the “demosession” session I have been using throughout the series).

While testing, I did run into an interesting anomaly so I had to add a little bit of awkwardness to the script. When comparing the original script for “system_health” (that can be found in the u_tables.sql script in the Install folder), to what was produced while scripting sessions from the GUI, I noticed that my script was recreating the session in the same fashion that the original script was doing it. But this did not match what the GUI did.

When scripting from SSMS (the GUI), the events in the session are ordered by Package and then by Event name. My script, originally, was ordering the events by Event_id which is the same as can be found in the u_tables.sql script. Due to this, I added a parameter to allow for the different sort methods. This also underscores that when a session is deployed, the order of Events in that first script is the order the Events will be created within the metadata.

I decided to output the results of the script via a print statement. This permits me a chance to see the entire script and review it. Then, I can copy to a new window and execute or I can save the script.

With this script, I can quickly do what is an essential function of a DBA, I can perform targeted backups of my deployed sessions. Another way of viewing it is that I can reverse engineer (without the GOOEY) any user definable session that is deployed to the server.

This is just one of many articles in this series. In this article, I demonstrated how to backup a deployed session and create a script for recovery purposes of a targeted session. To find more useful information about extended events, you can get caught up here.

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.

«page 1 of 96

October 2015
« Sep    


Welcome , today is Tuesday, October 6, 2015