Tricks and Treats with XE

Halloween is a great time of year. It is unfortunate that it is just one day of the year. That said, I do like to think of the phantasmripmonth of October as Halloween Month. I have several posts over the years that geek out over the cross-over between Halloween and SQL Server.

With the proximity to Halloween, it’s not hard (knowing me) to figure out why I originally decided to terminate this series on the Eve of Halloween day. Then again, as it turns out, I decided to bring the series back to life on the other side of Halloween. In short, it was to be killed on Halloween Eve and then implicitly converted to some living dead creature. You pick whether it is a zombie (which is far better suited to SQL Server) or a vampire.

If you are interested in the previous Halloween posts, here is a list of a few of them:

XE Related

Ghosts in your Database I

Ghosts in your Database II

Ghosts – an eXtrasensory Experience

DB and Fun Related

All Halloween posts

That list is my Halloween treat this year. Now for the trick with a very strong warning. Because of this warning, I am not posting any code showing how to perform the trick.



I have debated seriously over even publishing this “trick” for Halloween because of how deliciously evil it is. I will try and paint the picture in broad strokes. I will leave it up to you to connect the dots.


A third party vendor has provided an application along with some database requirements. Among these requirements is that the application account must use the ‘sa’ login. You have a strict policy that ‘sa’ must never be used for any applications or by the end-users. This is an extremely protected account by policy. The dilemma you have been presented is that the CEO insists that this application must be used (never happens right?) and the vendor insists the application will not function properly without the use of ‘sa’ (I can hear you chortle at that).

Now you, as the DBA, are stuck between a rock and an even harder place. Being smart (and somewhat smart and sadistic), you insist that the use of the ‘sa’ login should not be performed because it will break SQL Server (in reality we know the login does not break SQL Server, but something inevitably will break due to a mis-step by the person using it a) when they shouldn’t, and b) because they lack proper training). Inside you are grinning from ear to ear because you have some devilish ideas, some of which you learned by attending a session by Rob Volk (twitter) where he shows some devilish methods to deter the use of ‘sa’.

For the sake of the scenario, let’s just say you have a policy preventing the implementation of logon triggers (as suggested by Rob) due to a couple of mishaps a few months back. Somebody implemented a logon trigger that wasn’t properly configured and it happened to prevent all users from accessing the server (including the dba’s). Ruh roh!

And then…

Later in the week, after reading about the power of Extended Events, you decide to play around and do a little discovery. You come across this blog post that shows you how to find all of the possible events within the XEvent Engine. So you run the script that you found and shown here:

And while looking through the events and descriptions you discover that there is an event to log all of the successful logins. Not thinking anything of the third party vendor (because it just makes your blood boil) you begin to dream of the potential for auditing all successful logins (established connections) for all users and documenting who may be doing what and when.

After taking a few notes about the potential for the login event and getting access and logins under better control, you continue along your journey through Extended Events by reading this article about Actions. Feeling comfortable enough from the article, you do what any good data professional, trying to learn a new topic, would do – you start exploring using the provided query:

While looking through the available actions, you see one in particular that causes you to mangledraise an eyebrow (not both, just one). There is an action called “debug_break”. You squirm and ponder for quite a bit at the name and definition of this particular object. You wonder out loud “why would anybody ever want that in there?”

Your co-worker interrupts with “Do what?”

To which you reply “Oh nothing important. It was just something I read.” After which you dismiss it, realize the time of day, pack up and head home for the evening. Hours later after the work day has long since been finished, you awaken in a cold sweat with an “Aha!” that startles your slumbering neighbors dog. Immediately you pull out your laptop, connect to your local sandbox instance and get to work with a wry smile and devious giggle.

Upon returning to work the next day, you call the vendor and work out a solution to build them a sandbox server to provide a proof of concept. You grant them the use of sa to use for their application login. Sitting down together and connected to the new sandbox, the vendor attempts to login and the following happens:


Slack-jawed and speechless the vendor pales in complexion. Deep down inside you giggle like an elementary grade school girl – content with yourself. BATTLE WON!

After the vendor departs with a promise to rework the security requirements, you restart the service on the sandbox and go about the rest of your work week with a silly grin from ear to ear and nobody the wiser.

That concludes the trick. In the tradition of Halloween, you knocked on my virtual door of Extended Events and I, in accordance with the traditions of Halloween, gave you a trick and a treat.

I hope this helps you to understand the power of Extended Events. Thinking through a problem and reaching a solution is what XEvents can help to provide. I hope this Halloween XE article was enjoyable to you. Stay tuned as the XE 60 Day series goes full Zombie starting next week.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


Default Sessions

defaultsSo I have blasted through a lot of technical articles about Extended Events. There is a lot of groundwork that has been covered with layers of building blocks that have been stacked. Now it’s time to take a step back as I near the end of the original goal for this series.

I say original goal, but that really only means the goal pertaining to the length of the series. I had a goal for content that has yet to be reached. Due to that, I have pushed the series into overtime. So, really, that means this is merely a step back to a lighter topic today as I jump into half-time (or something like that) of the series.

Default Sessions

In the previous article I mentioned some of the default sessions for Azure SQL Database. I will not be going into any further detail about those sessions at this time. Discussion on those default sessions will have to be reserved for a later date.

The focus of this article will be to introduce the default sessions deployed with SQL Server. Some of these are internal and “private” while others are more “public” and like a user defined session.


The system_health default session is the most common of all of the default sessions. This session is akin to a true black box recorder in SQL Server. There is a ton of information that can be trapped via this event session. One caveat to this session is that if you are looking at the session in SQL Server 2008/R2 then the session really should be modified to include a file target.

sp_server_diagnostics session

The name of this session almost gives it away. This is an internal private session that executes a stored procedure by the same name: sp_server_diagnostics. Since it is a private internal session, I do not have all of the details on the internals. But since it is related to the sp_server_diagnostics stored procedure, it isn’t hard to make the leap at what it does. In addition, there are some events with a similar name which helps to better understand what is happening here.

The sp_server_diagnostics stored procedure captures diagnostic and health information about SQL Server to help alert to potential failures. This procedure runs continuously in repeat mode. Here are some of the results from my laptop for this procedure:


Notice there are five different domains of interest in this result set. If I look at the events by the same namesake, I will see the following possible data points:


Wait, there’s more! Diving in deeper into the event, I can see the following possible component_names / domains for the maps in this event:


In this last result set, there are multiple data points that map back directly to the stored procedure. In addition to the direct mapping, this result demonstrates the additional domains of “AGS” and “LAST”. I don’t have any AGS created on this machine so no stats would be recorded for that. I don’t know what “LAST” represents. Then the sp_server_diagnostics_state maps directly to the “state” seen in the stored procedure.

Having this same information divulged via extended events doesn’t end here. The system_health session collects the sp_server_diagnostics_component_result event by default. This all ties together back to the “sp_server_diagnostics session” which appears to be the manifestation of the “repeat mode” of the sp_server_diagnostics extended stored procedure. The proc is continuously running and gathering data for the event to trap the information and subsequently send it along to the event session targets.


The internals of this session are the hardest to track down. This is an internal private session. The name of the session does reveal a fair amount of information about the session that can be used to make decent educated guesses at what is being done here. First this session is used for monitoring In-memory OLTP / hekaton / hk (for short) tables and natively compiled stored procedures.

Taking the knowledge that this is used to monitor the “hekaton” engine, one can also deduce that the possible list of events comes from the following (probably others involved too):


A more complete list of events that seem to fit for this session can be retrieved via the following query:

The final piece of interesting information is that this session appears to be tied to the following dll: hkengine.dll. This is a dll that is loaded in SQL Server and it does bear the same naming convention. Here is another query that will show if this dll is loaded:

This session is only available as of SQL Server 2014.


The AlwaysOn_health session is very much like the system_health default session. This is a default session that can be scripted from within SQL Server Management Studio. Think of this session as a black box recorder for your Availability Groups. This session will capture events related to the state of the AG replica, if there is a state change, any errors that occur, and failovers that occur.

To get a grasp of what this particular session does (try and figure out a query to accomplish this before proceeding), one can run the following query:

And the results would be:


Bonus points if you recognized this could be done based on the foundations pieces laid out in previous articles.

This session became available as of SQL Server 2012.

The default sessions in SQL Server cover a lot of points of interest when looking to monitor a database environment. Between these sessions, this is quite a significant black box recorder for SQL Server. Despite the quantity of events that these sessions combine to monitor as your black box recorder, it doesn’t mean that you can’t add more events to monitor. That is the power of Extended Events. It is also important to remember that these sessions are default sessions. What do we like to do with most defaults? That’s right, we tweak it to be more specific to our own environments.

I hope you enjoyed the article, even without the dive into the default sessions of Azure SQL Database. Stay tuned because those sessions may be covered in some future article.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


Azure SQL Database

So far I have strictly focused on Extended Events for SQL Server installations. This means I have db_cloud_greenbeen looking at Extended Events from the “physical” or on-premises perspective. Installing SQL Server into an Azure VM falls into this category as well.

What hasn’t received any TLC is Extended Events from the Azure SQL Database perspective. You may be wondering why it matters since I just said that Extended Events is the same in an SQL Server in an Azure VM as it is on-premises. That is Azure and that is the cloud right?

Well, not entirely. Azure SQL Database works quite a bit differently. Azure SQL Database introduces some things into Extended Events that basically turn your XEvent world upside down. Sort of. If you understand that Azure SQL Database is a contained database (in concept), then some of this world flipping I mentioned is merely a tilt and not terribly earth shattering. That said, it does require at least a little bit of attention. Today, I will merely introduce some of the differences and then leave the internals for a later time.

Azure SQL Database


Azure SQL Database uses some of the same dynamic management views(DMVs) and catalog views as SQL Server. I covered those DMVs and catalog views in previous articles here and here – respectively. For the most part, Azure SQL Database uses a different set of views though. Some of the views of interest for querying metadata are listed in the following tables:

Running Sessions

DMV Description
sys.dm_xe_database_session_event_actions Info about actions within active event sessions
sys.dm_xe_database_session_events Info about events within active event sessions
sys.dm_xe_database_session_object_columns Info about event payload for events in an event session
sys.dm_xe_database_session_targets Info about targets within active event sessions
sys.dm_xe_database_sessions Returns a row for each active database scoped event session.

Looking at the names in the list they should be pretty familiar at this point. The real difference is that these are scoped to the database whereas SQL Server is not scoped in the same manner. This is an essential distinction due to the way Azure SQL Database works.

Deployed Sessions

Catalog View Description
sys.database_event_session_actions Info about actions within deployed event sessions
sys.database_event_session_events Info about events within deployed event sessions
sys.database_event_session_fields Returns a row for each customize-able column that was explicitly set on events and targets.
sys.database_event_session_targets Info about targets within deployed event sessions
sys.database_event_sessions Returns a row for each event session in the SQL Database database.

These particular catalog views should also seem somewhat familiar. The major difference in name being that they are scoped to the database instead of server just like the DMVs.

XE Internals Metadata

sys.dm_xe_map_values Returns mappings of internal keys to text
sys.dm_xe_object_columns Metadata for object data definitions
sys.dm_xe_objects Metadata for different available objects within XE Engine
sys.dm_xe_packages Listing of packages registered within XE Engine

And lastly, these are all the same between SQL Server and Azure SQL Database. If you have been following along with the series, these views really should come as no big surprise and there should be a base familiarity with them. That familiarity is part of the reasoning for skipping a deep dive at the moment.


Azure SQL Database still give the possibility of storing event data in multiple different types of targets. Not all standard public targets are available for user defined sessions. One can currently use the ring_buffer, event_counter, and event_file targets. This provides for two different memory type of targets and one file target. The file target does not come without a cost though. To use the file target does require access to an Azure Storage Container and will take some extra effort to properly configure.

Default Sessions

Azure SQL Database also comes with a set of default sessions automatically configured. Not all are running just like in SQL Server, but they are deployed. Even though several sessions are deployed by default, there is a limitation to how many can be running at a time (subject to change). The default sessions are (subject to change):

  • azure_xe_activity_tracking
  • azure_xe_errors_warnings
  • azure_xe_object_ddl
  • azure_xe_post_query_detail
  • azure_xe_pre_query_detail
  • azure_xe_query
  • azure_xe_query_batch
  • azure_xe_query_detail
  • azure_xe_query_execution
  • azure_xe_query_waits
  • azure_xe_query_waits_detail
  • azure_xe_waits


Despite these differences, there is a fair amount of consistency. Discovery of objects and metadata still remains the same. Being able to query the metadata for running or deployed sessions is also in line with the previous articles I have written in this series. In short, despite the slim differences (subject to change) and the platform differences between SQL Server and Azure SQL Database, there are still consistencies and common building block practices that I have shown still apply.


Even though there is a great amount of consistency throughout the extended events engine and between these two disparate platforms (SQL Server and Azure SQL Database), there is one glaring difference that must be discussed. I showed that the catalog views and DMVs are scoped to the database instead of server. Guess what that means for creating the actual session? You got it! An Extended Event Session is scoped/created at the database level rather than the server level. This is done easily as follows:

As I have said, I am not going to go deep into the details and inner working of Extended Events in Azure SQL Database at the moment. I will save those deeper dives for a later time. What I have done at this point is to introduce the basic differences and introduce the realm of XE in Azure SQL Database to you. Don’t be afraid to use Azure SQL Database based off a previous limitation around Extended Events. Things are changing in the cloud and they are changing at a rapid rate.


I hope you enjoyed the article.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


Exposure to Internals

engine_indbFor me, some of the most fun stuff with SQL Server is diving deep into the internals. Diving into the internals is not always an easy adventure. Sometimes, it requires a lot of trial and error sometimes. There are times where a dive into the internals can get me “into the weeds”, “down a rabbit hole” on a long windy path with no end in sight. Granted the end usually comes, but you get the idea.

That said, today I am not taking you on one of those journeys. The premise of the article today is to get a little exposure to some of the internals that may cause a bit of curiosity for you. I will share a means to expose some of the settings/stages/steps of various internal processes. These are the sorts of things you may have heard about, but without Extended Events, you might have to dig harder and further to find them.

Exposure to Internals

There are numerous events within the Extended Event engine that expose internals and internal operations. I will not be exploring the internals via actual events. That would be too easy! I want to share the internals available to you not via events, but rather other objects from the engine. Today, all of the internals will be exposed via the “custom” data types. More in-line with the official terminology is that these will be exposed via “Maps”.

The beauty of the maps is that these are free-floating maps. None of them are currently tied to an event. So trying to find an event that will help track these internals is just not going to happen. /* Disclaimer: This is only relevant to current versions. This is something that could change in future versions. */

Knowing those pieces of information, the dive into the internals becomes far easier now. I know (based on prior articles in the series) that maps are exposed via sys.dm_xe_map_values. Since they are not tied to events, I also know that there are currently no fields using the maps within sys.dm_xe_object_columns. Now I want to look at all the possible internals that are tied to maps. This can be done via the following query:

This will produce the following sample of results:


That is just a sample of the internals maps available through the XEvent engine. That is pretty cool. Looking closer at the results, I have highlighted (in green) some really interesting and cool maps. Wow! I can look at the different components of the query_optimizer_tree_id. That seems like a worthwhile look! Let’s look closer at query_optimizer_tree_id now.

I added a few more pieces of information to this query so I can get a bigger picture of the query_optimizer_tree_id map. I want to know the over-arching map name, the package name, the source dll, and I want to know what this map does. The results are as follows:


What a wealth of information into the internal workings of the query optimizer! I can see that the sqllang.dll is the source for the optimizer tree. I can see that this map reveals the different stages during optimization. And I can see that the Package is the sqlserver package in XEvents. This is fantastic! Of course, I may be a little geeky about it, but it is a cool way to get to know some of the internal operations of SQL Server.

What if I want to explore a different map? Well, I can pick one from the first query and then dive in and look just the same way I did for the query_optimizer_tree_id. In fact, let’s repeat that process with a different map, and with a slightly enhanced discovery query:

And the results:


Nothing really earth shattering there. It is just the database state after-all and it should be rather familiar to the data professional.

Some of the internals maps will definitely be far more interesting than others. And the interest level will definitely depend on the person browsing the data. For me, learning more about the optimizer and internals in general is a lot of fun. Consequently, I am drawn more to the maps that can give more information about the internal workings of the optimization tree or even the different operators (logical and physical) within a query plan.

This article demonstrated a quick means to explore various stages of SQL Server internal operations. Accessing this kind of information is just scratching the surface for internals and really just a scratch on the surface of Extended Events. There is a lot of power with the little bit of information that XEvents yields. Explore and enjoy!

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


Short Circuit Events

shortcircuitI introduced the importance about predicate order previously. I recommend either reading that article for the first time or reading it again if you have already read it. You can find that article – here.

The predicate order is critical. Having the wrong order can result in not trapping the desired events. I cover the critical nature of predicates in the aforementioned article. At least in principle. Today, I want to reinforce the topic through the use of examples.

Short Circuit Events

Housekeeping: For the examples, I will use two different Extended Event sessions. The only difference between the sessions will be the order of the predicates that I will use.

Setting the stage: I have a specific set of queries that are executing within the Adventureworks2014 database. I want to capture just the second query to be executed from that database. Since I want to capture just the second query to be executed, I will be using the package0.counter predicate.

This is an extremely simple scenario for a reason. The simple scenario helps to simplify the creation of the session. And of course, it also makes it much easier to demonstrate the effects of the predicate order.

Before getting into the weeds with the session, here are the basic query statements that will be used across the trials.

As you can see, I will be running the same set of queries multiple times with just the slightest of variation. I will run the first four for the first XEvent session I create which will have the counter listed first in the predicate. Then I will run the second four statements for the session configured with the database filter first in the predicate order.

Without further ado, here are the definitions of the two XEvent sessions:

With these sessions, I will only have one turned on at a time. Running both at the same time can result in invalid results. The process is simple: from separate connections, alternate through the list of queries and then check the session data to find the result. The queries are written to help indicate which query window each should be run. For the results to be valid, I will be sticking to that pattern. In this test case, the pattern will alternate between two connections where one is connected to the tempdb database and the other is connected to the AdventureWorks2014 database.

Running through the trials for the pred_ordercountfirst session, I will receive the following results:


I have cycled through each of the four scripts with the pred_ordercountfirst session in the running state. Notice that I have no event_data? This is because no event has been trapped. When the counter reaches a value of two, the second predicate condition fires. The second query to have been executed was actually run from the tempdb database. Because the second condition fails the check, no data will be captured for this first session. Recapping each iteration would look like the following:


Each query executed caused the counter value to increment as shown by the green box. In the red box is the second iteration which was performed against a connection to tempdb. And in the yellow box, I show that no statements have been trapped in this session.

Here is a query to help validate the session results.

I have not included a means to parse into the session data because there is nothing to parse. That will change with the next trial. I will drop the pred_ordercountfirst and then create the pred_orderdbfirst session. Then I will run the trial in the same fashion as I did for the pred_ordercountfirst session.


It’s a bit small, but the data is there. Looking down into the data a little closer, I will see the following:


From the session data, I can see that the overall third query to execute was the query trapped by the session. This is the correct result based on the order of executions and the requirements defined in the beginning. See the big difference between the configurations of the predicates in these two sessions? Predicate order is important and critical to a proper running event session.

To recap the results by iteration, I have the following:


Similar to the previous session, I have used green to highlight the counter value after each iteration. Since the first query executed was against the Adventureworks2014 database, the counter value incremented to 1. In the next iteration, it remained at one because the source database was tempdb and not AdventureWorks2014. Finally, the counter incremented to two on the next iteration when the source database was AdventureWorks2014. At this point, since both criteria were met, an event was recorded and sent to the event_file target (recall the event processing steps shown here).

To parse all of this data, I used the following statements:

In this article I have just shown how the configuration of predicates for each event will affect the events that are trapped. In TSQL both of these predicates would produce the same results. In Extended Events, the order of the predicates can cause a significant difference in the event that is captured and what is expected to be captured. Furthermore, this processing order can correlate to a difference in observer overhead since more (or less) events may be captured than what is expected. The more data that has to flow throw all levels of filters, the more impact that will be caused by the configuration of the event session and predicates.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.


«page 1 of 100

November 2015
« Oct    


Welcome , today is Wednesday, November 25, 2015