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.

The Extended Event GUI

In the previous article in this series I shared the basics around assembling an XEvent session. The method used in that article was via TSQL script. In this article, I will introduce the Extended Events GUI. While I prefer to use TSQL to create and manage my sessions, the GUI can help to visually better understand some of these concepts.

If you are a bit behind in the series, here is the table of contents so you can get caught up again. Now is as good a time as any to catch up on the series. right?

As I was saying, I prefer to use TSQL to create my XEvent Sessions. The biggest reason being that a script can be saved and used to easily recreate the session on multiple servers. The next big reason is that the script can also explain exactly what is being done to create the session. With the Extended Event GUI, extra steps need to be taken to ensure the session will be created as desired (albeit small steps but extra nonetheless). The third big reason I prefer to use a script is that the GUI was not available until SQL Server 2012.


Gaining access to the Extended Events GUI is rather easy to do from within SSMS. Simply expand the Management Node and then the Extended Events node. Once there, right click “Sessions” and select “New Session…” or “New Session Wizard” from the context menu. Here is a nice visual on that with the following image.


The option I will focus on is the “New Session…” option. I would hope that the Wizard is avoided if the GUI must be used to create sessions. Once “New Session…” is selected, the following screen will be presented to you:


In this image, I have color coded some of the sections of this screen to match the previous article and the components previously discussed. From this screen, one will not be able to see all of the components that compose an Extended Event Session. To reach the remaining components, a little more digging is necessary.

Also of importance is to note that a warning will pop up on this screen that after giving the session a name, the minimum requirements to create an XEvent Session have not been met. The minimum requirements being that a name and event be supplied to the session. Nothing said about the remaining components. This is a small niggle for me in that, while technically an Action and a predicate are optional, a target really should be defined (more on that in a bit). I also recommend that predicates be used because of the benefit to performance and event collection they can provide.

After giving the session a name, it is required to click “Events” highlighted in that orange-ish color in the previous image. This will bring up the next screen:


This screen will present you with many options / events that can be used in the session. In this example I have chosen the auto_stats event. By double-clicking the event or single-clicking then clicking the arrows between panes, the event will be selected and populate the right hand pane. Though hard to tell from this image, after the event is selected, the OK button becomes enabled and the session creation can be concluded here. I recommend not clicking that OK button – ever.

At this point, find the “Configure” button in the top right of the screen. This will bring us to the additional components that have been hidden from view to this point.


On the new screen, we can see the Actions and Predicates – color coded for ease of tying these components together as was done previously. A note on the Actions tab is that they are called “Global Fields” here with “Actions” in parenthesis. These actions can be viewed as “fields” that can be applied globally to many events.

After perusing the list of Actions, next up is Predicates/Filters. Clicking that tab will produce a screen like this:


Here I can select from any of the fields attached to the event payload or the Actions seen on the previous screen. This gives me the chance to configure multiple filters for this event to meet my specific requirements.

Next up is the Event Fields tab. This is the event payload.


In this image I have circled an interesting item in the payload. This item has a checkbox. This means the item is one of those boolean data types that is “customizable”. When I select this item, it performs a “SET” operation within the creation of the Event Session. I will speak more to that in a moment.

Let’s now turn our attention to the next screen – Data Storage.


When I open this screen, I will have multiple options in the type menu. For this example, I have only selected the event_file target. Once selected, there will be target specific options that populate the bottom section of the screen. In this case, I need to give the target file a name. I can supply a path or just a name. If I supply just the name, then the target will be created in the default “log” directory for the instance. If no target is specified, then a default target will be used. This is the event_stream target aka “Asynchronous live stream target.” I recommend specifying a target so the data can be stored and evaluated at a later time.

And now I will conclude with the Advanced options. While these are optional settings with defaults in place for them, I recommend at least taking a look to understand what options are available.


I won’t explain these options just yet. This is just for reference and introductory purposes. After looking at this screen, click “Script” and then Cancel. As I mentioned previously, I prefer to use scripts instead of the GUI. If the GUI is necessary, then script the session and evaluate the script. This is the “minor” additional step mentioned earlier.

Now looking at a sample script generated from the GUI:


In this script, I added an additional target, and multiple Actions over what was shown in the images for this demo. Apart from that, the rest remains the same. I color coded each segment of the script to not only map to the components of an Extended Event Session, but also to the color coded screens in the examples. In addition, I added notes about some of the “SET” operations and on which screens those can be found.

I have just shown how to configure a single event within an Event Session. It is important to mention that the configuration screen (with actions and predicates) needs to be done separately for each of the events within the session. This is reinforced by the script where it shows the Predicate and Actions are directly attached to the Event. This makes using the GUI a bit repetitive and slow if deploying several Events within a Session.


Assembling a Session

emptysession_puzzleUp to this point, I have taken a lot of time to discuss the various components of Extended Events. There is good reason for that. The components I have discussed are essential pieces of Extended Events and are critical to building useful sessions.

Previously, I only touched lightly on the topic of the concept of Event Sessions. In talking about the Sessions, I introduced means to see which sessions have been deployed, and which sessions are in a running state on the instance. But I have yet to explore the details of what a session is. It is now time to start diving into the details of what constructs a session.

Assembling a Session

event_puzzleThe first critical component of a session is the Event. I have covered the topic of what an Event is (here), as well as a deeper explanation into the payload of an Event (here). In short, the Event is an occurrence of something that interests you – the observer.

predicate_puzzleThe next component is the filtering mechanism known as predicates. I have covered this topic with the introductions into source predicates, comparison predicates, and predicate order.

Recall that after an event is collected, the next phase in the process is to evaluate the predicate(s). The predicate is a fairly significant component and should be duly considered when creating a session.

action_puzzleThe third component that I have previously introduced is that of the “Action”. An Action is an extra piece of data in the stream that is attached to the event payload.

The application of Action data to the session payload is the third step in the process. You can read more about Actions from here.

target_puzzleThe last critical component that I have discussed to this point is the Target.

A Target is the consumer of the event session data. It is crucial to direct the payload of the session to a consumer to be reviewed at a future time.

I introduced the need for the consumer when I introduced Targets – here.


3dmen_puzzleWhen we start to push these components together, one has the makings of an Event Session that can be used to trace and trap the events that are of interest to whatever issue may be important at the moment. Let’s see how this looks in the code.

In this session, I have determined that I want to trap the sql_text action on the second occurrence of a sql_statement_completed event in the AdventureWorks2014 database. When, this event occurs, I want the data to be consumed by both a memory target and by a file target. Notice that I have used multiple predicates and multiple targets.

Alternatively, let’s look at the script this way:


In this previous image, I have taken the code sample previously listed and color coded each session_puzzleof the critical components of an Event Session. Besides the color outlines, one could easily remark that the code looks pretty similar to other methods to create objects within SQL Server. When I create an Event Session, I use the standard DML syntax used when creating, altering, or dropping other objects like tables and procedures.

In my example, I have color coded each of the code segments to match the appropriate puzzle piece. For instance, the predicate is green. Notice that I used two types of predicates and even left a note about how I used the overloaded method for the pred_compare predicate type instead of the comparator object. Also of note here is that should I decide to send the session payload to multiple targets, that is well within reason as demonstrated here.

With the core concepts in place, building an Extended Event Session does become a fair bit easier.

To get a recap on this series, continue reading here.

Freecon – Oct 27 in Seattle

Comments: No Comments
Published on: September 23, 2015

Just over a week ago I announced that SQL Solutions Group was getting ready to host a day of training, FREECON if you will, in Seattle as a prelude to the PASS Summit of 2015. You can read all about that here, with registration being here.

While I really do hope to see the room fill with people looking to get some fantastic training, I also want to note that this training is not a part of the Summit. Though I hope it is viewed as an effort to enhance the learning made available during the week of Summit and to give the attendees more options.

An option is only really any good if some sort of detail can be attained about the option. While, I did publicize that the event was going to happen with a brief introduction into the the topics to be covered, one may be left wondering what really will be covered during the day.

Today, I hope to help make the SSG Freecon option a better option  by including a little detail into what it is that I will be presenting during my session on Extended Events.

Extended Events

This should come as a huge surprise given the 60 Days of XEvents series that I am currently publishing. While I am publishing a lot of information about Extended Events in this series, this Freecon session will cover the methodology that I, as a consultant, would use to troubleshoot various issues when a client seeks my help.

It is well known that there is a wealth of information within Extended Events. One can tap plenty of information about performance issues, errors, or general interest type stuff by setting up an XEvent Session and trapping some (hopefully) useful information.

roadmapDuring this session at the FreeCon, I want to help show how I would use Extended Events as a consultant to help find the pertinent information that will bring to light the problems the client is having.

How can I use Extended Events as a consultant to perform a health check on your server? That information will be covered throughout this session.

How can I determine where precisely the backup failed (and when) and be able to procure more information to determine if it was caused by some other event on the network? This is the type of information included in the methodology that I plan to share.

Think of this session as a condensed road-map to XE Success.

Comparison Predicates and Event Data

In the previous installment, I began covering the topic of predicates. In that article I demonstrated that there are multiple types of predicates. There are two objects that fall into the predicate category and then there are data comparison predicates (those that are not objects).

The two objects are pred_compare and pred_source. With pred_source having been covered in that first part, I will now cover the pred_compare object as well as the standard predicate that is not an object.

In that previous installment, I associated predicate to a filter. I also mentioned that a predicate in Extended Events is a means to short circuit event evaluation. I want to underscore the importance of that feature.

If you imagine a complex conveyor belt system as if it were the Extended Event engine, you might see something like this.


Packages roll along the conveyor system and will reach various check-points. At each check-point, a bar code is scanned and the package is moved merrily along its way.

There can be many paths for a package as it rolls along from start to finish within a network of conveyor belts.

Now, focus on the scanner as I have done with the next image.


This scanner is the filtering mechanism. If the bar code of the package meets the requirements to be rerouted along a different belt, this scanner will be able to make that happen. The package will be scanned, meet the redirect requirements, and then the scanner flips a mechanical switch to redirect the package.

This is not too different from Extended Events. When the payload of the event (package) meets the predicate (scanner) requirements, the payload is sent down a path to a pre-defined target. If it doesn’t meet those requirements, the event just passes by as if nothing happened (other than being scanned).

Now consider the predecessor (e.g. the method employed by Profiler/SQL Trace). The filter mechanism routed everything to the same spot and then attempted to apply the filter after the collection was made. The new filter process, employed by Extended Events, is many times more efficient and less impacting than the method employed by Profiler and SQL Trace.


Much like the object name says, this type of predicate represents the different types of comparison operators that can be performed within a predicate. The pred_compare type can be written in multiple ways. The easier approach for most would be to continue writing a predicate in the same fashion as for a standard TSQL query. In other words, these predicate objects are overloaded with the standard operators that could be used within a typical TSQL WHERE clause.

Let’s look at how to view some of these predicates:

Exploring through this list of predicates, one will see 77 different compare predicates. Here is an abridged example of that list.


Reading through the list, one could quickly figure out that the less_than* and greater_than* comparators would map to the < and > operators respectively. Despite the easy mapping to the overloaded counterparts of the operators, the way to use these is slightly different than with good old TSQL syntax. One needs to think more programmatically to utilize these types of objects.

Suppose I wanted to use one of these objects when trying to find all of the stored procedures taking more than 500 milliseconds. I would need to write my predicate like the following:

The alternative (and more common approach) would be:

Now suppose that I go ahead and use the easy syntax (TSQL syntax) to create a predicate on the sp_statement_completed event and deployed that session to a server. The event engine will take that overloaded syntax and do a translation for me. The syntax that it will produce would be of the following nature:


In this particular example, I chose to have two predicates. The two are a bit different, and the deeper explanation will be discussed in the future. The simple explanation for the difference is that one is based on an Action and the other is based on an event. Looking at the predicate within the green box, one will see that the sp_statement_completed event has a predicate on it that uses the greater_than_uint64. Then the field that is being compared is added within this XML. The last piece of this leaf node is the value. I decided to go for 25 seconds of run-time instead of 500 ms. (Note the values here are in microseconds and not milliseconds.)

All of that is generated from the predicate I wrote as “duration > 25000000” when creating the event. Whether using the more familiar TSQL syntax of the XE syntax to build your compare predicates, it will be translated and stored in an XML format using the actual pred_compare objects. Keep this in mind when building predicates.

Earlier, I stated there are two types of predicates to discuss in this segment. The examples I just showed are an illustration of those two types of predicates. Call it standard TSQL syntax vs. the XE syntax for the comparison operators within a predicate. That’s really what it boils down to.


While on the topic of predicates within Extended Events, there is a limitation set on the size of the predicate that should be brought into consideration. This limitation is 3000 characters. Because of this limitation, it is highly recommended to use the standard TSQL syntax where possible (granted a 3000 character predicate is rather large). The pred_compare objects are a bit lengthy in name when compared to the symbol equivalents and could consume predicate space rather quickly.

Predicates is a pretty important topic. And due to the importance of the topic, I will be discussing this topic again in the future.

To recap on the entire series, please see the “Table of Contents“.

Storing Event Data

Among the several core concepts of Extended Events is the concept of storing event payloads / data. When an event fires, it would not be far-fetched to want to be able to review that data at some time after the fact. How often are you sitting there watching the system just waiting for an error or event to occur? Hopefully, you aren’t just staring at the monitor waiting for it to happen.

A thought comes to mind for this kind of monitoring – “a watched kettle never boils.”target And while that is not entirely true, the intent is close. It seems that while you are watching for an event to occur, it just doesn’t happen. Or at least, it waits to happen at the precise moment you blink and consequently you miss it.

Being able to store the session data is essential to a good monitoring solution. Within Extended Events, this storage comes via the concept of targets. Recall (from the Objects Article) that a target is the destination of the payload and thus is a consumer of the event payloads.


Microsoft has provided several consumers for us to use when creating Extended Event Sessions. In addition to the targets readily available to use when creating a session, there are a few that are “hidden” targets and are reserved for internal use. Private or public, we will take a quick peek at targets as a whole, but we will spend most of our time looking at what is available to us as a consumer.

So what kind of event consumers / targets can one implement to direct output from a Session? Let’s see:

This will produce the following results (well, if you are using SQL Server 2008 or R2, your results will vary):


This provides an array of options with some nice opportunities to do different things with EXtended Events. One can either direct the event payload to memory, to a file, to a counter object, or try to match the event with another event. These are some good options. However, if you are using SQL Server 2008, your results would have been different. Let’s compare and contrast real quick.


While, the same general targets are available, they are implemented differently across the versions of SQL Server. This is important to remember when trying to recreate an Extended Event Session on SQL Server 2008R2 that was designed on SQL Server 2012, and was set to output to an event_file target. Trying to recreate that session can result in a loss of hair due to the difference in target names between versions.

I mentioned earlier that there are some targets that are “private” and basically not configurable for an event session. What are these targets? That part is easy enough to figure out. Let’s adapt the previous script just a bit.

And here is the list of those private targets:



For those that are Profiler junkies and love to watch events stream in “live”, Extended Events has just the target to do that. The event_stream “private” target, attached to the event session by default and not configurable, will allow one to watch events as the trigger. By now, you have likely also noted that there are a bunch of targets listed with the term “audit” in the name. I will dive into this more in the future, and at that time it will become more apparent why it is not directly configurable through the creation of a user defined Extended Event session.

Stay tuned for future articles where I will go into the use of these various targets and how to attach them to a Session.

Extended Events and Fixed Schemas

Working with databases, XML, and many software applications, it is not uncommon to deal with fixed schemas. In the database world, it is fairly fundamental to have some sort of schema. The schema represents the typing of the bit-strings that are being stored or presented. One might even call it the template.

With the fundamental nature of schemas within a database, it should come as no surprise that Extended Events also has a means to expose the schema of many of the components that can be found within the XE engine. To access the stored schemas, one would use the DMV, sys.dm_xe_object_columns.

This DMV exposes the fixed schema that is available for the object/component for the different data groups. If you recall from the previous article, there are three data classifications (readonly, customizable, and data) that could be present for a particular object.

Not all objects have a fixed-schema that will be exposed through this DMV. In fact, the types of objects that will be exposed are the target, map and event object types. When considering the different components, this stands to reason since many objects infer a single point of data and not necessarily a schema (we will be discussing those other components in the near future).


The exploration of the schemas tied to the different objects is pretty straight forward. Let’s begin with the most basic version. I will not focus much on what is exposed in the DMV since this is basically the schema and that concept should be fairly straight forward to most DBAs. For instance, a schema will contain a basic model of the the columns and data types and relationships. The schemas in XEvents is very similar.

This being a basic query to look at the schemas of the components available in extended events, it should be plain to see that nothing is being excluded from the results. This means we could be seeing the schema for both events and targets. Recall that there are only three types of objects which have stored schemas.


Note here that I have referenced back to the sys.dm_xe_objects DMV, This is done to ensure I can get the object types. That piece of info needs to come from the xe_objects DMV. This DMV will be used throughout the remaining examples too.


Let’s complicate things just a little bit. If I try a little more complex query and want to eliminate the target and event schemas as shown in the following, I will get an interesting result.

This will return no results. Now, there should be something returned since I should see some map schemas. Yet, this query returns nothing. As it happens, there are maps that share the same name as some of the events.

I can see that little piece of fun by altering my query to the following:

And the results:


This is good information to know if one of these events that shared a name with a map was needed for a specific monitoring solution being explored. It is easy enough to code around, but it can be just a bit of a nuisance if the name duplication remained unknown.

Taking this information into account, I can now query the schemas more precisely and get a good feel for what kind of schema is going to be available for the component.

With this type of query, I can now query the schemas in a better way. I can look at all of the data groups, or a specific data group. I can also look for a specific object type / component. This type of query will become an essential building block as we get into trying to figure out which object will best fit the needs of the problem being investigated or the trace desired to be created.

Stay tuned for more articles as we prepare to discuss actions, targets and data types.

Extended Events – Event Anatomy

So far in these brief discussions about Extended Events, I have covered several core concepts including a basic introduction into what an Event is within Extended Events. For a recap on those articles, you can read this.

As a follow-up to the introduction to what an Event is, it makes sense to discuss the components of an event. What makes up an event? What is the payload of the event? What is a payload?


The payload, is the stuff that is to be delivered somewhere. The payload can be rather extensive and broad like one may see in the armament configuration of a B-2 (as we see in the image to the right).

Or the payload can be more streamline and compact as one might see in a rocket used to deliver a satellite.


As seen in this image depicting a typical delivery mechanism for satellites to be delivered beyond the Earth’s atmosphere, the payload can be very specific and “targeted”.

With these payloads, we see that some common payloads range from one point of interest to hundreds of points of interest.

When working with computers (and not a physical delivery mechanism), a payload can work in much the same way. The item to be delivered can be focused and small in quantity or it can be broad and large in quantity.

A common use of payload in the computer world is with a virus or malware. Virii have a payload that is delivered to the new host to attempt to infect it. This is not too far different from the payload of Extended Events – excepting that XEvent payloads do not infect new hosts.

The payload of an XE Event is the data that was captured at the time the event (point of interest) was fired. The data of an Event can be broken into three classes or groups. More specifically, the columns that define the data can be broken into two different types with two divisions in the second type.

The first is the readonly data of the event. These columns are much like the metadata of the event containing data such as the channel and keyword. This data is used for ETW integration.

The second type of columns is the data that is captured when an event fires in the engine. Within this group, there are customizable data points as well as state data points. The data that is customizable is to the point of enabling the capture of that data point or disabling the capture of that specific data point.

These different types do not apply to all events. The customizable data may be present in some events. Don’t be surprised if you come across an event that does not contain a customizable data point.

We can look at some of this via the following example. Note that while this example touches the sys.dm_xe_object_columns DMV, I will not go into great detail about this just yet.

This will return the following results in SQL Server 2014:


In the attached image, I have color coded each of the different types of data. In the results, it is also shown that the customizable data points are boolean in nature. They are either enabled or disabled for the capture of that data point. And lastly, we can see the “metadata” or “event header” type of information in the readonly type of data. Of these data points within the payload, the most interesting will consistently be found in the “customizable” and “data” classifications.

The information presented on this payload will prove more helpful as we continue on this series. Stay tuned for the next article when we start taking a look at the Columns in greater detail.

Extended Events Categories

I recently delved into a brief explanation of Packages as they correlate to Extended Events. In that article, I left a couple of things un-said about packages – leaving them for this article.

Packages have a couple of descriptors (or classifiers) that can be useful to help figure out what events contained within that package might be related to other events. This would be particularly helpful in putting together a session to troubleshoot a specific issues.

The classifiers within packages are called keywords and channels.


A channel in Extended Events can be very closely defined just the same as a radio station or a TV channel. The channel indicates a stream of interest or in other words it identifies the audience of interest to the event.

Term Definition
Admin Events that are targeted to the administrators and support. These events indicate a problem with a solution that an administrator can act on. These events typically have a message associated with them telling the consumer what to do to resolve the problem.
Operational Events that are used for analyzing and diagnosing a problem. One may use these to trigger tasks based on the occurrence. Think Administrator when considering who the consumer might be.
Analytic Use these in performance investigations. These are high volume type of events. Think of anybody working to resolve performance issues as the consumers of these types of events.
Debug Debug events are used solely by developers to diagnose a problem for debugging. Think CSS when dealing with these.

Depending on the day or the issue, your interest point may be in a different Channel. Keep that in mind as we progress through event classification.

You can find these channels within the XEvent metadata by performing a query similar to the following:


The term “keywords” should invoke a common meaning for most people. If you perform internet searches, you should be familiar with what a keyword would be in “Google” or “Bing.”  It is a search phrase that would yield a group of results that are related to the term that describes the problem you are researching.

Within Extended Events, the Keyword is a fine-grained classification of the events. When used in conjunction with the Channel, it becomes more refined and as a result a better classification of the events being sought.

To discover these keywords, one could execute the following query:

Within SQL Server 2014, that will yield 53 unique keywords. That doesn’t tell the entire story though. Notice the emphasis on unique. A keyword may exist in more than one package and be applicable to more than one Channel.

To see this a little more clearly, we could do something like this:


Notice the highlighted results? We see that query_store (even in SQL Server 2014) has a few results as a keyword. There is even what appears to be a duplication within the sqlserver package for that keyword. Well, if we explore that a bit further and add the Channel, we should see more clarity.

This would reveal to us the following:


Taking note of the highlighted rows again, one can see that the query_store keyword affects multiple Channels along with the multiple packages. This will be useful after we start to learn more about events. Once we do that, we can learn to correlate the events to these channels and keywords.

There are many useful keywords coupled with the four channels. We can see through various queries that the keywords are reused between packages and channels. These keywords and channels offer a granularity in helping to search for events and group events into common themes.

Other than the usefulness in searching for like events, why do we have this kind of granularity? Well, as it turns out, this was done to have Extended Events follow the same setup seen in Event Tracing for Windows (ETW). This also allows an integration between Extended Events in SQL Server and the tools available for ETW. Think of it as one step in an effort to bring sysadmins, developers and DBAs to a standard means of monitoring and tracing on the Windows platform. You want to be able to work efficiently with the sysadmins and developers, right?

Stay tuned for the next article where I will discuss events.

«page 1 of 21

October 2015
« Sep    


Welcome , today is Sunday, October 4, 2015