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.

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.

Predicate Order is Critical

Comments: No Comments
Published on: September 23, 2015

I have mentioned predicate order previously and wanted to make sure I drive home the importance of the predicate order when dealing with Extended Events. This is an important topic because it can be very helpful in maintaining a reasonably performing event monitoring system.

The proper order of a predicate will be the difference between enabling the Event Engine to short circuit an event or whether it will cause it to labor needlessly and add to the monitoring overhead.

filtration_systemTo illustrate this more clearly, I want to first evaluate how a water filtration system works. I will try to do the image justice, and bear with me for a bit.

Within a water filtration system, there are several filters in place. Each filter helps to remove a different set of particulates. Each progressive filter is designed to remove subsequently smaller particulates.

So, in the design of water filtration, at the top level, the filter is nearly wide open allowing all but the largest particulates to flow throw to the next level. This is the method employed at each level of the filtering process until at the end we have nothing but water.

If one were to employ that design in an event monitoring system, this would equate to the funnel design where the largest quantity of events possible are allowed through the first filter. This is a close assimilation of what we see with Profiler. Let everything in to the filtering system and then eliminate the least matched items first until the criteria most likely to fail (or most unique) criteria are finally filtered.

That sort of methodology works well for a water filtering system because it really needs to pred_conefilter out the larger items first as a matter of efficiency. Within a monitoring system, it is
more useful to eliminate the finer grained events first. Let me try to explain while using this next image.

In this type of approach, the largest items are excluded first. This isn’t large as in size since we are talking about streams of data. But rather we try to first eliminate the items that are least likely to be part of the desired result. Then the filtering becomes less restrictive as we flow down through the system.

By eliminating the larger amounts of noise first, this means less items flow through the filter to the next level of the filtering system. This is the method employed by Extended Events. I will explain in more detail in just a moment.

First, let’s examine briefly the processing steps within Extended Events.


Notice, from this layout, that the predicate is evaluated third. This evaluation occurs prior to the application of the action data and prior to allowing the data to hit the targets. If the predicate were to be evaluated after actions, one could imagine that the overhead would become a little bit higher. And should predicates be evaluated after writing to the targets, the overhead would again be higher.

That’s a good optimization for the event engine. Short-circuiting of events helps to further reduce the load in that third step because it allows even fewer events down the filter process before they can even get on to that next step in the overall process.

Short Circuit

If I have a server with 500 databases and I am interested in finding all of the queries that happen to be taking more than 25 seconds to complete for my ClientOrders database, I could try to find all of those through Extended Events through a couple of means. First, with very little attention to the order of the predicate:

Given that I have 500 databases on this instance, I could have a pretty large set of events that would now need to flow through to the next predicate. Let’s just assume that I am managing an extremely poorly performing Instance and all databases have frequent occurrences of queries taking more than 25 seconds. Since only one Business Unit really cares about how poorly their application performs, I really need to find their queries that are performing terribly – and I must do so with minimal impact to the server.

Now, if I write the predicate as follows:

I have decreased the flow of events through the filtering process that would reach the second filter phase. This can lead to a more lower impact on the instance that may already be struggling given the problems I have already described.

How have I decreased the flow of events through the filtering process? This is the short-circuit in action. More accurately, Extended Events will evaluate the order of the predicate in the order that it is declared. By putting the most restrictive (or most likely to fail) predicate first in my predicate declaration, it will be evaluated first and subsequently short-circuit the event evaluation process whenever my database id does not match.

With all databases having performance issues and likely to have queries that exceed 25 seconds, my best option to short-circuit was to single out the database first since it is only 1 of 500 databases on the instance. This short-circuiting is sure to be a departure from expectation for many database professionals that are accustomed to the Query Optimizer re-ordering the predicates of queries automatically.

In the second example, I also showed the addition of an action to the event. I did this to underscore the processing steps. With predicates being evaluated prior to action data being attached, this means that action data does not get attached to unwanted events and subsequently causing performance issues due to that. This is a second degree of short-circuiting.

A good predicate in Extended Events will be of great use in your troubleshooting and monitoring efforts.

Stay tuned for more on Extended Events. If you have missed any articles in this series, please check out the Table of Contents for the series.

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“.

«page 1 of 52

October 2015
« Sep    


Welcome , today is Sunday, October 4, 2015