Reading Session Data from Memory

In the last few articles I took you through a progression of working with the event_file target from the most basic concepts on through the more advanced. While working with the event_file target, I showed that XML is central to working with the session data for that simple_ringbuffertarget type. XML is not just a fundamental component of the event_file target, I have also shown that it is ingrained throughout Extended Events. That indoctrination includes the topic of this article – the ring_buffer target.

I took you on a bit of a journey while discussing the event_file target. The journey for the ring_buffer is going to be much shorter. A big part of the reason is that I have already laid a big part of the foundation due to the similarities with the event_file target. This is just another step in the progression and continues to build on principles already shown.

Before diving in to the session data, a quick glance at what the ring_buffer is would be helpful. The ring_buffer is a memory target. In addition, as the name implies, the ring_buffer can be overwritten due to the nature of the ring. Once the buffer fills, then the new events will have to go somewhere, and that means something gets purged or overwritten.

The next important note is that it is a memory target and that means it is volatile. If the session is not running, well then the target won’t be there. This means that the only way to read the data in the target is to parse it while the session is running. In addition, since it is volatile, if the server is bounced then the data in the target is purged.

These are also a few of the reasons contributing to a shorter journey about this particular target. When working with this type of target, great care needs to be taken to ensure the session data is scraped and that the target is configured with attention to detail.

Reading Session Data from Memory

Since the target is only available while the session is running, I will only be focusing on the DMVs where the metadata for running sessions resides. In this case, the key DMV will be sys.dm_xe_session_targets. From there, I can run a very similar query as was done for the event_file, with the exception that I do not need to rely on a function to get the session data.

Unlike the event_file target where a bit of XML held the information for the file name and path is exposed via the target_data column, the entirety of the session data will be exposed for the ring_buffer via that column. This means I can use a query such as the following to retrieve all of the session data.

And now, much in the same fashion as the event_file target, I can throw that into a query to parse the session data into an easier to read format.

As you can see, I went straight to the more optimal means of parsing the data. I chose to dump the session data into a temp table first and then from there I can more efficiently parse the XML into a more friendly format. Notice that I did need to use one more trick to parse the XML. I implemented the APPLY operator in order to break down  the session data into the various event nodes.

ringbufferwconsumerI want to return to the behavior of the ring_buffer in order to help underscore the potential for missing any events that may have been trapped.

I mentioned that the target is volatile due to the nature of it being a memory target. What I did not mention was that unless there is a consumer to fetch the events from the session, it is highly probable that the events will be missed.

This goes hand in hand with the circular nature and volatility of the target. In order to effectively use the target, you have to watch it and retrieve the events from it on a regular basis. Otherwise, it would be as if the events were never recorded.

I have shown in this article how to read session data from memory. I have also pointed out some of the less risky pitfalls of this particular target. Similar to the event_file, one will need to become familiar with how to parse the data with XML.

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.


Better Practices for Reading Event_file Targets

I have demonstrated over the past couple of articles the basics involved with reading the
event_file target along with a more advanced technique.

db_xesession_xmlIn each of those articles I outlined some problems that could be encountered. Some of those pitfalls might include another DBA moving the location of the files and not documenting the change (the documentation still needs to be done but it takes a few extra moments to figure out the new location and can be frustrating) or potentially that the session is no longer active (this can be bothersome and cause a bit of hair loss). Both are legitimate concerns and can be solved, it just takes a little more planning up front to prevent the problems.

In this article, I am going to address some better practices for Reading Event_file targets attached to an XEvent Session. These better practices include how to retrieve the data file and directory dynamically regardless of the running state of the session. In addition to that, I will show a method to help parse the data more quickly.

Dynamically Read event_file Data

Similar to the prior article, I will take the basic example provided previously and use it as a starting point.

Now that I have the basics in place, I need to find a different means to find the filepath based on the name of the session that does not require looking at just the running session metadata. For this, the filename is not as obvious as you may think. Logic may dictate that the data should be in the metadata for the session target, or sys.server_event_session_targets, but that isn’t the case. You may recall that I had mentioned that in a prior article in this series. The source of this data happens to be found in the sys.server_event_session_fields view. Since this is an EAV model, it also means that I have to more or less look for some value in the “name” filed that indicates it is the target file path. With that in mind, I can then create a query such as the following to get my first building block in place.

The results of this method are pretty clear and simple. I have a file and path directly without the need to parse any XML as was necessary with the method shown that involves querying the running session metadata. Since the data is coming from an EAV model, the value column is defined as a sql_variant data type so I need to convert it. Additionally, I need to add a little trickery to my query to help it find the actual files on disk.

Notice in the results that the original filename is returned as the value. This does not include any of the additional information that is appended to each file in the target. Because of this, a quick REPLACE can be used to insert a wildcard into the filename. This is shown in the next example:

This is now returning the same sort of results as should be expected for any data in the target for the session. All that is left to do now is tie that back into a query that will parse the XML from the target file(s).

Great! I now no longer need to know exactly the path for the session in order to query the data in it. In addition, it doesn’t matter if the session is running or just merely deployed. This adds the additional benefit that I can run the session to trap the data I hope to get, then stop the session when I think I may have it. Once done, I can evaluate the data and not have the extra overhead of the session running while I look into what has been captured.

This brings us to the next better practice to use when working with the session data. If there are a lot of events in the target to parse, then it can be painfully slow to retrieve the data to evaluate. There needs to be a means to doing it faster.

Faster Session Parsing

This is a really easy fix. The recommendation to help speed things along to read the session data is to first dump the data into some sort of table storage. That table can either be a temp table or a permanent staging table. The decision is up to you! For my example, I will just use a temp table.

This is how I would do it:

This will convert the event_data field into NVARCHAR in the temp table. This means I will need to CONVERT it back to XML in order to parse it. And then to get to that data for my parsing query, I just add it back in as shown in the next example:

I have left the sp_help in the example as a quick means to go back and verify that the event_data is truly NVARCHAR, should you choose to do so.

Adding this little step of dumping the data into a table first helps improve the queries to parse the event data by a factor of 3 and sometimes more.

I have shown in this article how to parse session data more efficiently. I have also shown how to ensure the correct file and path can be used every time to get the session data by using just the session name. These tips will help ensure a more pleasant experience when trying to parse the event data from a session.

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.


Dynamically Read event_file Data

xe_filetarget_boxIn the previous article I covered the basics on extracting the payload for the events in a session from the event_file target. That article was a basic introduction.

In this article I want to take that basic knowledge and take it one step further. Being able to extract the data is fine and well. Being able to just do that task at the basic level is hardly sufficient, nor is it very efficient. I’ll explain that part in a little bit.

Pulling the data from the event_file target in the most basic of manners requires that one know the directory where the files are placed and the names of the files for the session. Granted, one should know this information if they are the one to have created the session. What if you didn’t create the session? What if you inherited the server with a bunch of sessions already deployed? Worse yet is the case of the magically changing file or location for the session (you know, somebody changed it and didn’t document the change).

There needs to be a more efficient means of grabbing the file without having to know the location. Knowing the session name should be adequate enough to parse the payload data from the target. This is exactly what I am going to show in this article – retrieving the file and path based strictly on the name of the session.

Dynamically Read event_file Data

I will take the basic example provided previously and use it as a starting point.

Now that I have the basics in place, I need to see how I can get the path of the file based on the name of the session. Looking in sys.dm_xe_session_targets, a column called target_data is revealed. On closer inspection, it is apparent that this column contains xml data and part of that data involves the location of the event_file. With the first clue out of the way, I can build a query such as the following to help build my file path dynamically.

Clicking on the “hyperlink” XML in the target_data column will give me a result such as this:

This is good stuff so far. Now I need to be able to tie that into the previous “base” type query. This can be done beautifully thanks to the APPLY operator. Here is an example of that:

Knowing that I need to follow the path //EventFileTarget/File, I can take advantage of parsing the XML via the APPLY operator and then pass the node data to the next APPLY which is where I am calling the fn_xe_file_target_read_file function.

Running the query will yield a row for each event in the session should there be any events that have occurred since the session was last started.

Now that I have two stages of this thing built, it the next step is ridiculously easy. All that I have to do now is bring in the columns that I want to query from this session.

Great! I now no longer need to know exactly the path for the session in order to query the data in it. Despite that, I really should figure out what that path is and make sure I have it documented. I now want to draw attention to the comment made at the beginning of the last script. This particular script will only work when the session is running. Don’t despair, I will cover how to build this dynamically for any session that is not currently running. That will be covered in the next article where I discuss “Better Practices” for reading event_file data.

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.


Auditing Needs Reporting

Comments: No Comments
Published on: October 13, 2015



Welcome to the second Tuesday of the month. And in the database world of SQL Server and the SQL Server community, that means it is time for TSQL2SDAY. This month the host is Sebastian Meine (blog / twitter), and the topic that he wants us to write about is: “Strategies for managing an enterprise”. Specifically, Sebastian has requested that everybody contribute articles about auditing. Auditing doesn’t have to be just “another boring topic”, rather it can be interesting and there is a lot to auditing.

For me, just like I did last month, I will be just doing a real quick entry. I have been more focused on my 60 Days of Extended Events series and was looking for something that might tie into both really well that won’t necessarily be covered in the series. Since I have auditing scheduled for later in the series, I was hoping to find something that meets both the XE topic and the topic of Auditing.

audit_wordcloudNo matter the mechanism used to capture the data to fulfill the “investigation” phase of the audit, if the data is not analyzed and reports generated, then the audit did not happen. With that in mind, I settled on a quick intro in how to get the audit data in order to generate reports.


An audit can cover just about any concept, phase, action within a database. If you want to monitor and track performance and decide to store various performance metrics, that is an audit for all intents and purposes. If you are more interested in tracking the access patterns and sources of the sa login, the trapping and storing of that data would also be an audit. The data is different between the two, but the base concept boils down to the same thing. Data concerning the operations or interactions within the system is being trapped and recorded somewhere.

That said, it would be an incomplete audit if all that is done is to trap the data. If the data is never reviewed, how can one be certain the requirements are being met for that particular data trapping exercise? In other words, unless the data is analysed and some sort of report is generated from the exercise it is pretty fruitless and just a waste of resources.

There is a plenitude of means to capture data to create an audit. Some of those means were mentioned on Sebastian’s invite to the blog party. I want to focus on just two of those means because of how closely they are related – SQL Server Audits and Extended Events. And as I previously stated, I really only want to get into the how behind getting to the audit data. Once the data is able to be retrieved, then generating a report is only bound by the imagination of the intended consumer of the report.

SQL Server Audits

Audits from within SQL Server was a feature introduced at the same time as Extended Events (with SQL Server 2008). In addition to being released at the same time, some of the metadata is recorded with the XEvents metadata. Even some of the terminology is the same. When looking deep down into it, one can even find all of the targets for Audits listed within the XEvents objects.

Speaking of Targets, looking at the documentation for audits, one will see this about the Targets:

The results of an audit are sent to a target, which can be a file, the Windows Security event log, or the Windows Application event log. Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records.

That doesn’t look terribly different from what we have seen with XEvents thus far. Well, except for the addition of the Security and Application Event Logs. But the Target concept is well within reason and what we have become accustomed to seeing.

If the audit data is being written out to one of the event logs, it would be reasonable to expect that one knows how to find and read them. The focus today will be on the file target. I’m going to focus strictly on that with some very basic examples here.

I happen to have an Audit running on my SQL Server instance currently. I am not going to dive into how to create the audit. Suffice it to say the audit name in this case is “TSQLTuesday_Audit”. This audit is being written out to a file with rollover. In order for me to access the data in the audit file(s), I need to employ the use of a function (which is strikingly similar to the function used to read XE file targets) called fn_get_audit_file. The name is very simple and task oriented – making it pretty easy to remember.

Using the audit I mentioned and this function, I would get a query such as the following to read that data. Oh, and the audit in question is set to track the LOGIN_CHANGE_PASSWORD_GROUP event.

There are some tweaks that can be made to this, but I will defer to the 60 day XE series where I cover some of the tweaks that could/should be made to the basic form of the query when reading event files / audit files.

XE Audits

Well, truth be told, this one is a bit of trickery. Just as I mentioned in the preceding paragraph, I am going to defer to the 60 day series. In that series I cover in detail how to read the data from the XE file target. Suffice it to say, the method for reading the XE file target is very similar to the one just shown for reading an Audit file. In the case of XEvents, the function name is sys.fn_xe_file_target_read_file.

Capturing data to track performance, access patterns, policy adherence, or other processes is insufficient for an audit by itself. No audit is complete unless data analysis and reporting is attached to the audit. In this article, I introduced how to get to this data which will lead you down the path to creating fantastic reports.

Intro to Reading Event_File Target Data

xe_filetarget_boxHaving covered the metadata for a deployed session from just about every angle (yes there is more on metadata that could be discussed, has not yet been discussed, and that I am not planning on covering at this time), it is high-time to start figuring out how to query the payload that is desired to be trapped by the session that was deployed.

Early on in this series, I introduced the catalog views, DMVs and the supporting cast for Extended Events. In those articles introducing the supporting cast and views for XEvents, there is a critical player in this arena that was intentionally neglected. That player is the function that helps one to read the payload data that was captured via the session definition. That function is sys.fn_xe_file_target_read_file.

Functions and Files – an intro to Reading Event_File Target Data

The event_file target is an asynchronous consumer for Extended Events. This target stores the received payload in a proprietary binary format. Because of this, one needs to use the sys.fn_xe_file_target_read_file function. Using the function will then convert the data into a somewhat usable and more friendly format called XML.

With the session data being in a more human friendly form, a little bit more work needs to be done for that data to be really helpful to the data professional. To be able to get the results into a format more conducive to the consumption of most data professionals, one must use XQuery. This article is just going to focus on getting the data out of the event_file target and into the XML format. Working with the XML is a topic for another time.

The sys.fn_xe_file_target_read_file function takes a few parameters. The most important of these parameters are the first two parameters. And even then, that only applies to SQL Server 2008 and R2. Since SQL Server 2012, one really only needs to focus on the first parameter – path. That said, the file_offset can be an extremely useful field, especially under the circumstances where the session data is to be “warehoused” or a monitoring and alerting solution is to be built from Extended Events.

The path parameter is used to specify the on-disk path to the trace file that has been created. Not just the trace file, but all of the trace files associated to the session. A single file name (with path) can be specified here. Or a more common use would be to add a wildcard to the file name (with path) so all files could be included. If a wildcard is not used, then the initial_file_name parameter is pretty useless since only one file will be read anyway. If the wildcard is used, then the initial_file_name parameter can be used to determine the starting point for reading the session data.

The second parameter is mdpath and is only applicable to SQL Server 2008 and R2. This is to specify the path of the metadata file that would have been created along with the event file as a part of the session in those versions of SQL Server. If SQL Server 2012 is being used, then this parameter is unnecessary.

The initial_offset parameter helps instruct the function what to ignore and what to process when reading in the session data from the target. If storing all session data into a table (basically warehousing the data), this is an extremely helpful parameter. The use of this parameter would help the import process only import new data from the target. It would be a nightmare to import the same monitoring data every time the load process ran.

Using the same demosession session that I have used throughout the series, here is a basic example of how to retrieve that session data.

In this example, I am just pulling the session data straight back without any manipulation. I have CONVERTED the event_data to XML only as an exercise to make it easier to evaluate. If I did not convert the event_data, it would return an XML string in NVARCHAR format.

If I needed to be able to explore the data in a more friendly format, then I need to shred the XML such as I have done in the following example:

Using the function in each of these ways will work consistently and reliably. One major drawback is the need to know what the filepath is for the session in question. Even if the filepath was known at one time and saved in a script (such as the previous examples), it is not far-fetched to have another person to change the filepath and not notify anybody or update any of the scripts.

In the next article, I show a more robust means to access this target data from the files.

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.


Simplified Session Backups

Comments: No Comments
Published on: October 12, 2015

posh_DBPowerShell exposes scads of information and provides the professional with a formidable tool for the tool-belt. Over the past several articles, I have demonstrated how this tool can be used to uncover useful information and metadata.

The groundwork has been laid to be able to mine information about each of the core components for Extended Events Sessions. These core components are Targets, Events, Actions, and Predicates. Being able to query this metadata and understand what it represents is an essential skill to more effective Extended Event Session management.

Thus far, I have demonstrated how to perform metadata inquiries from both PowerShell and TSQL. I even showed how to map this metadata between script and the GUI screens. As I concluded the metadata discovery via TSQL segment, I demonstrated a very useful script, to generate backup scripts of deployed sessions, using all of the concepts discussed to that point. That script can be found here. In this article, I will share how to do the same thing via PowerShell.


Before I go into depth on the script within PowerShell that can be used to backup a deployed session, I want to share a bit of a story.

Many years ago while still in High School, I had a really tough teacher for Calculus. He had a reputation going back to long before I took any of his classes of being a very hard teacher. I enjoyed this teacher quite a bit. My group of friends and I even enjoyed giving him a hard time because it seemed like he could take it as well as dish it out a little.

This teacher had a few philosophies that I didn’t really much care for at the time, but I can sure appreciate now. Truth be told, I gained an appreciation for his teaching philosophies within the first year out of High School. One of his philosophies was to make the High School classes at least on par with what could be expected in College. This made college significantly easier.

Another of his philosophies was to teach core concepts first then to build on those concepts. While teaching the core concepts, he always showed the hard way of doing things first and sometimes an equally hard method as the secondary approach. But there was always an easy way of doing things that would be eventually taught. Sometimes it is essential to a solid foundation to know how to solve a problem the hard way before learning how to do it the easy way.

Correlating that to SQL Server, there are many times that the first approach to do something may be a more difficult approach. As the skills are learned, then maybe an easier solution may present itself, but there is (hopefully there is) a solid foundation there first to help support and enable the learning of an easier method.

Simplified Session Backups

In the aforementioned article, I shared a script that would recreate a deployed session via TSQL. That script was lengthy and required a few tricks to get things to work out just right (e.g. concatenations). Now I will show how to do the same thing in a sickeningly simpler way.

The example that I am about to share requires that SQLPS be loaded and the “demosession” Session be loaded into an object. If a recap of how to do that is needed, I recommend reading this article prior to proceeding.

With the “demosession” Session loaded into an object, I also want to load the name of the session into a parameter to simplify things just a bit.

I can confirm the value of the $sessionname parameter if I choose. In this case, the value does return as “demosession” and is the correct value needed. With that, now I can run the following script to create a backup of my deployed session:

That is the entire script! Just one line! This script could have been even smaller, but I felt it necessary to dump the script out to a SQL file in order to have the script available for future needs – should it be needed. One caveat here is that the directory path must exist or an error will be thrown. To create the directory path, it is as simple as the old DOS commands of old to create that directory.

While the script produced is perfectly viable to recreate a deployed session, it is important to note that the Events listed in this script will be in the order of Package then Event sorted alphabetically. Recall from the prior article on Session backups that this is the default script order used by SSMS as well and is not necessarily the same order that the original Session was created.

How did I arrive at this method to create this script? This goes back to repeating some of the processes I have used throughout these articles on PoSH and Extended Events. Starting with a quick exploration into the $sessionobject object.

This would lead me to seeing the following results:


Seeing ScriptCreate as an option is a great indicator that I can do something with this Session to quickly recreate the session script. If I run that with the session obj, then I get the next clue.


I highlighted the interesting clue to this. Even though it says ScriptCreate, it apparently needs to have an additional command in order for it to do anything. With that in mind, a quick Get-Methods on ScriptCreate() will produce insight that there are commands that can be utilized. One of those commands happens to be GetScript(). GetScript seems pretty self-explanatory, so I would settle on that and voila the script can be reproduced.


There it is. I have shown two methods to create backups of deployed sessions. The first being the more complex solution and then this extremely simple solution. Both are valid means to produce the backup scripts and it now just boils down to a choice as to which to use.


This is the latest article in the 60 days of XE series. If you have missed any of the articles, there is a recap for the series. To recap or reread the articles in this series, please visit the table of contents.

Deployed Target Metadata with PoSH

Comments: No Comments
Published on: October 9, 2015

posh_DBHaving just covered the methods to discover the metadata for a deployed session including the details about the session settings, events, actions, and predicates, I am nearly finished with the session metadata. All that remains is to dive into the metadata for Targets.

Through the articles thus far, I have shown some of the nuances in dealing with PowerShell to retrieve this metadata and how it differs a bit from performing the same inquiries via TSQL. PowerShell exposes Extended Events differently than what one would expect due to familiarity with Extended Events through the catalog views and the DMVs I have previously discussed.

Along with the nuances associated with accessing this metadata via PoSH, I have also showed how some of this metadata is different. The dive into the metadata about Targets will continue to reinforce and embrace these differences.


Picking up from where I left off, and following the same pattern as has already been target_metadataestablished, it is time to dive into the metadata for the Predicates and Actions that are tied to an Event deployed with a session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

With that loaded, I just need to remember to reference the Events object (as was introduced in the prior article).


While the Actions, Predicates, and Events are all tied tightly together (Actions and Predicates have to be attached to an Event), Targets are somewhat independent. The Target is directly attached to the Session in the same way that an Event would be tied directly to the session. Looking at the Metadata for the Session attached to an object, it will show the Targets object being similar to the Events object. Let’s see that in action:

That script will produce the following:


Reverting back to the patterns used so far through this series of posts, I will explore what is available to me for the Targets that may be deployed to the session of interest. The following query would be the first step:

This query will result in the following:


Following the established patterns and results, I can see some very common ground here. The results of this query are strikingly similar to the results seen when querying Actions and Events where I would also see the “name”, package and a description. This is a pretty basic demonstration into viewing the targets tied to a session. To get a better view of the Targets attached to a session, I need to dig a little bit deeper.

This will produce the following useful information:


From those results, I can see that I would immediately be interested in the TargetFields (recall the similar property from the Events) and the Properties property. The first of these that I want to explore is the Properties property.

Note the trick I am using again to dump the results into a quasi-table format. And the results will look like this:


Once again, the results should seem somewhat familiar. These properties underscore one of the nuances being the EAV model with how PoSH accesses the Extended Event properties. With this kind of query, I can see what kind of targets have been deployed to the Session and get a little bit of information as to the type(s) of target(s) they are.

This is all really cool. The real meat of the metadata for Targets is in the “Set” options that have been configured. The “Set” options are the configurations that are exposed via the TargetFields property previously seen. With that said, it is time to dive into these configuration options.

This query returns this rather useful data:


And to make this slightly more useful and more valuable, I need to know which target has which setting. For a more usable query, I can resort back to the table output. Instead of just using the data directly accessible from the previous query, I will use the Parent property to get the name of the Target.

And now I will get something like this:


This is good and useful information. Being able to tie the target type to the specific setting is crucial to better understanding how the metadata is interrelated. This is also something pretty easy to do via TSQL as I demonstrated in this article.

In this article I have covered the core concept of Targets. I showed how to access the Target metadata which includes the “Set” operations for each of the Targets. This is the last of the core concepts to discuss the means to access the metadata via PowerShell. I do have one more item I wish to discuss via the means of PowerShell and I will be discussing that in the next article.

Stay tuned as I continue to work through another trick that can be done via PowerShell in the next article. To recap the series, please visit the table of contents.

Deployed Action and Predicate Metadata with PoSH

Comments: No Comments
Published on: October 8, 2015

posh_DBI have recently shown that using PowerShell can be extremely powerful in obtaining insight into how to investigate deployed Extended Event Sessions. Throughout the demos I have used, I hope that it has also shown that PowerShell can be very easy to use.

Querying this metadata through TSQL is easy, but using PowerShell does seem to simplify this just a step more. The simplicity of PowerShell is a different kind of easy over TSQL. For me, the simplicity of PowerShell comes in the quantity of code. That said, I want to reiterate what I said previously – there is a bit of a learning curve with PowerShell. PowerShell exposes Extended Events differently than what one would expect due to familiarity with Extended Events through the catalog views and the DMVs I have previously discussed.

I have demonstrated how to access session information as well as how to access Event metadata. That said, I left the discussion of Actions and Predicates for this article. As I expose this information, the simplicity of PowerShell will continue to be reinforced – along with the patterns that I hope have started to become apparent in the previous articles.


Picking up from where I left off, and following the same pattern as has already been 3d_toolestablished, it is time to dive into the metadata for the Predicates and Actions that are tied to an Event deployed with a session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

With that loaded, I just need to remember to reference the Events object (as was introduced in the prior article).


Looking back at some of the metadata that was revealed for the Events object in the previous article, I have the following:


If I query the Events directly, I should be able to see that there is a Predicate returned. Unfortunately, the formatting of the output is not very friendly and much of the information is truncated. Seeing the Predicate listed as another item that can be queried direct is somewhat useful. In addition there is a PredicateExpression property that could be useful.

In this following image, I show both the queries used for each (Predicate and PredicateExpression) as well as the corresponding output.


Notice how the PredicateExpression is a bit more familiar in the output? With the wonky formatting of the output for the predicate, one could also presume that this output would be the XML type of formatting for a pred_compare object. With a little extra effort, this can be confirmed.

And with better formatting, here are those results:


These results would reinforce the prior presumption. And it also helps to reinforce the differences between how the Predicate is represented in PowerShell over the sys.server_event_session_events view where the predicate column is closer in value to the PredicateExpression in PowerShell. Additionally predicate_xml in the the view more closely fits the Predicate property within PowerShell. As long as one can keep these differences in mind, it will make working with PowerShell and Extended Events a tad easier.


Accessing the metadata for Actions via PowerShell is a little bit more straightforward in that there aren’t multiple objects like with Predicates. Unfortunately, with Actions, it is right back to the EAV data model with the Actions object which can cause a different level of complexity and possibly confusion.

Starting with a very basic query to the Actions:

And the results of that query:


In addition to a problem noted with the Events (package name being listed twice), there is an additional problem with the initial output here. The problem with the default output is that the Event name is missing. Granted, this is also a problem with the Predicates examples too. This is one of those areas where extra effort has to be made when using PowerShell to explore Extended Events. Luckily, the data is there. All that is needed is to know how to get to the data.

So how does one figure out the Event for which the Action (or Predicate) was added? I have that in this next example:

This query is simply requesting the Parent (Event) of the Action, and then the Action name to be returned in a table format. Here is what the output should look like:


Having the Event name is a critical piece, especially in a more complex Session such as the system_health default session. After-all, an Action (yes it must be repeated because it is a core concept) is only attached to an Event and has to be configured for each individual Event.

Circling back around to the EAV concept. One of the areas within Actions where this is more evident is when trying to look at the properties of the Actions module. Starting with a sample query such as this:

Again, I am instructing the results to be returned in a table format. If I don’t do that, then everything is returned in a single column and it is far more difficult to pick out the needed bits of information. Forcing the results to a table format, I get the following:


Looking at these results, I see Name as a value under the Name column and the associated value for it under the Value Column. Then there is a lot of repeating. This means that a little extra work is going to need to be done to parse things a bit further.

In this article I have covered two of the core concepts related to deployed sessions in Extended Events – Actions and Predicates. To be able to effectively use PowerShell, I have also covered a few of the nuances necessary in figuring out where essential metadata is exposed via this tool.

Stay tuned as I continue to work through some of the Target metadata in the next article. To recap the series, please visit the table of contents.

Exposing Deployed Event Metadata with PoSH

Comments: No Comments
Published on: October 7, 2015

posh_DBIn the last article I introduced a power tool that can be used to help manage Extended Events. That tool is PowerShell. In that article, I focused primarily on introducing PowerShell as a power tool to help in discovering the Extended Event Sessions deployed to the server as well as some of the settings that are associated with a session. This is the same as thing as calling these settings the Session metadata introduced here.

Now that some of the basics concerning how to access Extended Events via PowerShell have been covered, it is appropriate to start digging in a little deeper. This deeper dive will continue in the direction of metadata discovery and familiarity with this powerful tool.

More Power

Picking up from where I left off, and following the same pattern as has already been established, I want to now 3d_toolstart diving into the metadata for the events that are tied to a deployed session using PowerShell. This means that I will need to ensure I have a session established that has the SQLPS module loaded. Then I need to ensure I browse back to the sessions folder.

Just in case, here is the working directory for the sessions out of the default instance.

Also, from this point, I will go ahead and load the “demosession” session into an object again for demonstration purposes throughout the remainder of this article.

In the previous article I noted that within the metadata for the session, there is a listing of some objects that can be further perused, which are related to the session itself. Those objects are Targets and Events (shown in the next image). I want to immediately jump into the Events object and see what needs to be done within PowerShell to access the metadata for the deployed Events.


Luckily, as with how things work from TSQL queries into the metadata, there are patterns that can be established within PowerShell as well. In this case, to get to the Events within the Deployed Sessions, I would need to “query” that object by adding it to the “query” that I use to list the contents of the “Sessions” object already declared (the $sessionobj instantiated previously). This is a lot easier to explain via the code.

See the pattern? This is the same sort of pattern that was used when querying the various deployed Session settings in the previous article. With the Session loaded in an object, I can query properties or sub-objects by appending that “object” or “property” at the end of the $sessionobj object I created. Running that last snippet will produce the following output.


Immediately visible are a couple of interesting tidbits. Beyond the query returning the list of all of the events in the session, I can see the package names, the predicates and the descriptions of the events. This is similar to the sys.server_event_session_events catalog view excluding the descriptions. Unfortunately, the formatting is a bit awkward so extra measures would be needed in order to get that description to be useful – just the same as would be needed when querying via TSQL.

Additionally, there is a bit of redundancy with this object. There is a listing for the package name for each of the events. Now, take a look at the event names in these results. I cheated a little bit and highlighted the interesting parts. Each event will have the package name as part of the event name within the PowerShell results. Again, just a note because it is one of those things that could cause a bit of grief later when trying to build more complex queries via PowerShell.

Looking a little bit further, if I continue to follow patterns established thus far, I can check the Metadata for  this “object” and learn a little bit more.

And the results:


This should come as no surprise. Actions are tied to specific events so it makes sense that it is an object accessible via the events object. In addition, I can see how the “SET” operations are tied into the Event. The difference here being that the object is called “EventFields”. This should make sense since this metadata is exposed in TSQL via the sys.server_event_session_fields catalog view.

And if I invoke a Get-Methods on the Events Object as follows:

I will get a clearer picture of the metadata and what I can do with the Events.


In addition to the Actions and EventFields objects, I can see the Name and Predicate properties just the same as I would if I were to query the session events through the catalog view – sys.server_event_session_events.

With the connection to the event fields being so much more obvious through PowerShell and thanks to the prior article on the event fields / “SET Operations”, I want to explore this metadata at this point.

The results of this will be:


From these results, I can see that the “customizable” data point “collect_database_name” for this particular event has been enabled.

In this article I have shown how to begin the exploration into the metadata for deployed Session Events. Additionally, I demonstrated how to quickly get to the set operations tied to those events. I have not yet covered the core concepts of Actions and Predicates which were exposed through some of the queries demonstrated in this article. Tune in for the next article where I will cover both of those topics in greater detail.

With all of the information that has been revealed through this series, it is easy to have either missed some  of the information or to have sensed a bit of information overload. If a recap is needed for one of these or any other reason, feel free to catch up here.

Extended Event Management Power Tools

Comments: No Comments
Published on: October 6, 2015

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

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

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

Management Tools

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

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


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

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


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

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

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


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


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

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

And this would show me the following output:


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

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

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

«page 2 of 3»

October 2015
« Sep   Dec »

Welcome , today is Sunday, June 7, 2020