XEvent Session Supporting Cast

So far, in this series, I have discussed the means to view the metadata associated with Extended Events. I also demonstrated some quick and simple means to retrieve some metadata via the catalog views and DMVs.

Beyond the metadata in the the catalog views and DMVs, there are a few more objects that come in handy when dealing with Extended Events. I call these the supporting cast. This cast would come in the form of yet another DMV and a couple of tables. Granted this is not the end of the tools that can be used when dealing with Extended Events, the remaining tools would be classified differently and are going to be saved for another time.

Supporting Cast


First up in the supporting cast role would be the group of tables. These are not your ordinary tables. These tables could be your best friend as you work to convert yourself away from server side traces and profiler traces. These tables contain conversion (and therefor static) data to help translate from server trace events to extended event events (yes it is extended event events – sounds redundant but that is what it is).

The tables you will come to love are:

Name Description
trace_xe_event_map One row per Extended Events event that is mapped to a SQL Trace event class.
trace_xe_action_map One row per Extended Events action that is mapped to a SQL Trace column ID.

And, as you might have imagined, the quick and easy way to view what tables might be related to extended events is fairly straight forward as well.

If I query the trace_xe_event_map table, I will see the mapping between 138 trace event ids to the extended event events along with the package through which that event is exposed for extended events. Don’t worry about packages just yet, I will discuss that at a later time. If I do the same thing for the action_map table, I will see a similar result.

As I mentioned previously, these tables will be essential in converting any of those old profiler or server side traces into the XEvent platform.


Of all of the XEvent DMVs, all but one are used for the metadata of running sessions. The remaining DMV deals more with troubleshooting and session performance than it does with metadata. The name of the DMV is sys.dm_os_dispatcher_pools.

Why is this DMV thrown down to the supporting cast role? Well, despite (current) documentation, this DMV is not exclusively used by Extended Events. You will see dispatcher pools and threads for various different background tasks like XEvents, In-Memory OLTP (Hekaton), and filestream – to name a few.


The preceding results could be viewed using a query similar to the following:

If it seems there may be a problem with the server and you just don’t see events dumping into the target as quickly as you think they should, you may want to check this DMV. In the DMV, one will find things like queue_length and dispatcher_waiting_count. A high waiting_count would indicate no events are firing. While a high queue_length would indicate there are many events backing up while waiting to be processed.

Stay tuned for more quick tidbits as I continue this series.

Viewing Running XEvent Metadata

Comments: No Comments
Published on: September 3, 2015

In the previous article, I wrote about getting into some of the metadata to take a peek at session metadata relevant to deployed Extended Event sessions. Today, I will continue talking about metadata. One difference being that today I will focus on diving into the metadata of running sessions.

Running session metadata, while it can be still be viewed in the catalog views, is exposed via DMVs. Putting it a slightly different way, if I query DMVs that are related to extended events, I will only see metadata for running sessions.


Similar to the catalog views, there is a consistent naming pattern and a simple query to help us figure out what is out there related to Extended Events.

That little query will show us a list of DMVs like the following – descriptions added.

Name Description
sys.dm_xe_objects List of objects exposed by an event package.
sys.dm_xe_object_columns schema information for the objects.
sys.dm_xe_packages Lists all the packages registered with the Extended Events engine.
sys.dm_xe_sessions Lists the Running Extended Events sessions.
sys.dm_xe_session_targets Information about session targets.
sys.dm_xe_session_events Information about events tied to a running session.
sys.dm_xe_session_event_actions Information about the “actions” tied to an event in a running session.
sys.dm_xe_map_values Provides a mapping of internal numeric keys to human-readable text.
sys.dm_xe_session_object_columns Shows the configuration values for objects that are bound to a session.

Since much of this is information about running sessions, it becomes very useful in trying to query the metadata for those sessions. However, if the session is not running and you don’t realize certain DMVs (e.g. sys.dm_xe_session* named DMVs) are only applicable to running sessions – you could end up being rather frustrated.

So, in a similar vein to the query presented in the previous article; if I want to query the DMVs to find where I might be storing session data, I would run something like the following.

This query will show us the file location for each of the deployed sessions that have data saved to a file on the operating system (as opposed to memory). But look at the results real quick. The target_data is more than just the file path. Sure you can manually parse that information from the results. Or you could go back to just using the query provided in the previous article. Or, get daring and try a little XML parsing – with this query.

And here we have yet another one of those frustrating things when dealing with Extended Events. Not only do the DMVs just show the information as it pertains to running sessions, but you also have to deal with XML now. Don’t think this is the last of dealing with XML in XEvents – not by a long shot!

This is just one quick example of the type of data that can be viewed from the running session metadata. Imagine wanting to figure out how long the session has been running. Or maybe you want to know if there is something that might be blocking an event from firing. The data in these DMVs can help you access that kind of data – quickly!

Stay tuned for more XE content soon to come!

Viewing Deployed XEvent Metadata

Comments: 1 Comment
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.

Shredding Extended Event Actions

lovehatedice_v1The other day I wrote about that torrid love/hate relationship DBAs tend to have with working with XML. In that same post, I promised I would have a follow up post about XML in Extended Events.

Well, today I present to you another opportunity for you to renew your love/hate relationship with XML.

In the previous post (which you can read here), I discussed just one facet of shredding the XML related to Extended Events. Today, we have “actions” to discuss. I am not going to get into what an “action” is today. I will save that for a near future post.

For the shredding of the actions, I will use the TreeHuggerCPU Event Session I used in the last article (for the sake of consistency). The session doesn’t necessarily need to be running. I will just pull the actions related to the session from the metadata accessible via the system catalog views.

Should the session be running (and in my case it is for demonstration purposes), I could open the session data and view it as XML and see something like the following:



I have highlighted two different types of nodes available in the XML data. In the previous article, I discussed the “data” nodes and I have highlighted that in red here. Today, we are talking actions, and those are highlighted in green this time around. It is the “action” nodes that we will be shredding via the following script.


With this script, I can either search for all actions tie to an XE Session, for the actions tied to one event within a Single XE Session, for all actions tied to a specific event across multiple sessions, or for all actions that are tied to any event tied to any event session deployed to the server.

Combine this with the previous script and suddenly all of that XML just got several times easier.

Shredding XML in XEvents

lovehateOne of the biggest pains with Extended Events is the thing we love to hate – XML. XML is so foreign to many DBAs. It’s not relational and often brings nightmares of parsing and performance issues.

Despite that, Extended Events takes advantage of XML much like we have seen in so many other areas of SQL Server. You might be familiar with execution plans, SSRS, SSIS, or maybe even the ring buffer. If you look hard enough, you will find XML within the database engine. Whether you love or hate it, you still have to deal with it. Today, I want to dive into a means of dealing with XML, as far as Extended Events is concerned.

Mad Scientist Lab

Let’s head on over to the lab to dive into XML head first. I will be using a session as an example of which I have previously written – here.

If the session is already deployed – great. If not, you may need to create it to execute (successfully) these scripts. Note that I am starting the session and then fetching some data and then stopping the session. To see some data, you may want to wait a few cycles before stopping the event session.

The sole purpose is just so I can take a look at the session data in XML format. Now that I have some data, I would see something that might look like the following:

For today’s lab, I just want to focus on the “data” node while saving the attributes of the event node, and the action node(s) for another discussion.



The “data” node happens to be the data that is directly tied to an Extended Event event. When looking at the event metadata, this would be called the event columns. Knowing that all of these columns follow a fairly standard format can make it a bit easier to figure out how to query this data. One of the daunting things with XML is figuring out how to query the XML data to make it more relational – a format we may be more accustomed to seeing (as DBAs).

Due to the daunting task of figuring out how to query the XML and because it is a pretty decent format for consumption, I decided to simplify the entire process. Why not write some code that will write the XML parsing code for me? And that is what we have here.

This script will take an event session name, an Extended Event event name, or a combination of both (imagine having multiple events tied to a session) to produce the XML strings automagically. This script does only produce some pretty generic column aliases, so that part is left to the user of the script to edit after generating the XML parse statements.

With this script, I can quickly retrieve all of the XML parse statements for all of the data nodes within the session or event that I specify. This can significantly reduce the amount of time taken to produce a usable script to consume the event session data.

This is just the script to parse the event data. If there are actions tied to the session, this will not produce the statements for those actions. The script for that will be provided in a future article. Stay tuned!

«page 1 of 91

September 2015
« Aug    


Welcome , today is Saturday, September 5, 2015