Session Action Metadata

Comments: 2 Comments
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:

sessionactions_results

 

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: 2 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: 2 Comments
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:

sessionview_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.
  4. EVENT_RETENTION_MODE accepts ALLOW_SINGLE_EVENT_LOSS, ALLOW_MULTIPLE_EVENT_LOSS, and NO_EVENT_LOSS as values where “ALLOW_SINGLE_EVENT_LOSS” is the default.
  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.

The GUI

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.

gettoXEgui

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:

session_gui

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:

events_gui

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.

actions_gui

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:

predicates_gui

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.

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.

targets_gui

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.

configs_gui

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:

script_session_colorcode

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:

session_code_puzzlecoded

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.

event_processing_steps

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.

pred_conveyor

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.

pred_conveyor_zoom

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.

Compare

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.

less_than_i_sql_ansi_string
less_than_equal_i_sql_ansi_string
greater_than_i_sql_ansi_string
greater_than_equal_i_sql_ansi_string
like_i_sql_unicode_string
like_i_sql_ansi_string
equal_uint64

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:

pred_translate

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.

Considerations

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

Predicates and Event Data

Comments: 2 Comments
Published on: September 21, 2015

At one time or another, a predicate has been the best friend or worst enemy of the data professional. If the predicate was forgotten during an update for a specific user account, then the predicate suddenly became the worst enemy. On the other hand, when the predicate was properly used it was indeed the best friend.

What is this predicate thing though? A predicate is a filtering mechanism used in SQL Server to target specific data when either querying or updating rows. A good filter can contribute to both data quality and improved performance. In the case of Extended Events, a good filter can help the event engine provide a more granular result set and a better performing event engine.

In Extended Events, a well defined predicate can allow for an event that does not meet the predicate criteria to be ignored and thus have a lower performance impact than other tracing mechanisms. The way this works is by allowing the most likely to fail predicate to be evaluated first. If the most likely to fail is evaluated first then a lot of events can just be looked over without needing to proceed to the next filter requirement. This is called short-predicatecircuiting. In other words, if 90% of the events do not meet the criteria for the least-likely to succeed predicate, they will not be evaluated by the remaining predicate conditions.
If you happen to be familiar with the filtering mechanism in Profiler or SQL Trace, this should be a welcome change. In addition, predicates in Extended Events allow for a much more complex configuration. Each event can have a different set of filters (predicates) or no filter at all. Recall how a filter worked in SQL Trace? One filter to rule them all (within the session), ring a bell? That is no more. This should be another welcome change!

Predicates

Extended Events comes with two types of predicates. In the section on objects, I wrote about a means to see the different components of extended events. Within the list of components, I listed pred_source and pred_compare as two of the components in Extended Events. These can be seen with a query such as the following:

To explore the various types of predicates that are available, we can expand the previous query. Given that there are two types of predicates, I am going to explore them separately. For this segment, I will only look into the pred_source predicate type. Here is a sample of how to explore which predicates exist in this type.

This would yield about 44 predicates that are in the pred_source type. What exactly is this type though? This kind of predicate unfortunately does add a little bit of overhead to process because of the attachment of data to the payload first – and then the filtering of that data. When dealing with these predicates, think of them as attaching “source” data for the source of the event. This is an additional payload of data very similar to the action. These are so similar to actions in fact that of the 44 predicates, 36 of them also exist as actions.

A demonstration of seeing this correlation can be seen with the following query:

When exploring those results, the main differences between the matching actions and predicates will be in terms used in the description. You will see that one may say “Get” while the correlating object may say “Collect.” That really is a minor difference and should give a decent idea of what these pred_source predicates will do.

Stay tuned for the next article where I will dive into the pred_compare type as well as plain old event data predicates.

Here is a recap of the series in case you missed it.

Map Data Types in Extended Events

Comments: 1 Comment
Published on: September 18, 2015

In the last segment, I wrote about the data types for the payload of an event in Extended Events. In that article, I also mentioned there is another component that is like a custom data type. That component is a map.

Maps are like a way of overloading the data type to expose more meaningful values that relate to the payload data in question. The standard data types can also be called scalar types with a single value (instead of a table lookup).

Maps

Looking at the internals for the maps is very similar to the scalar data types. All that will be necessary is a quick swap on a couple of values in the queries we use the last time.

Since I am just querying the components library in this query, I only need to look at the “map” object_type. This will produce a result-set of 254 maps. That is far too many to list here at the moment. But let’s take that and compare it to the maps that are attached to actual event payloads.

When this query is executed, it can be seen that there are roughly 212 maps exposed to the event payloads (or targets). This leaves us with 42 that for one reason or another are not assigned to a payload and may be unnecessary in the components DMV.

Looking into what maps don’t map to payloads with the following query, we should start to see where the differences may lie:

If you run that query, it will result in 43 maps that exist in the components DMV that do not exist in the payload DMV. Conversely, running this next query will help to explain why the math has not yet lined up for us:

I wanted to share this to show where the difference was. And looking at it, it is now apparent that there is a map in the event payload that does not seem to be in the objects. For me, this underscores the importance to use the payload DMV to see these maps in lieu of the components DMV (e.g. use sys.dm_xe_object_columns in lieu of sys.dm_xe_objects).

mismatched_map

This shows that the missing map is used by several of the events in the system.

With these maps, what can we do to explore the data related to the map. Notice that the previous query references the sys.dm_xe_map_values DMV? Well, this is where the map data will exist. Let’s browse a bit.

In this case, I have a need to try and figure out what the various values might be for the different maps tied to a specific event. I have chosen “database_mirroring_state_change” to see what data values will be available for the various maps.

sample_map

In this sample, we can see there is a column of data in the payload of the database_mirroring_state_change event. The map_value represents the friendly text of the event payload that we would want to lookup in order to determine what is happening (in this case) with the mirroring session.

Keep these few tidbits in mind as we progress through the series. I will revisit these maps in the near future as I continue to use these concepts to build on within the realm of Extended Events.

«page 1 of 3








Calendar
September 2015
M T W T F S S
« Aug   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, December 17, 2017