Today’s Subject: Predicates

Don’t worry, I haven’t suddenly switched the topic of this blog to English grammar, but if you want to learn more about Predicates as they apply to sentence constriction you can start with this article on WikipediA. Rather, today’s topic is about predicates in Extended Events. You can find a brief description of predicates in Using SQL Server 2008 Extended Events (by Jonathan Kehayias) so I’ll try to extend your knowledge a bit beyond what that says.

Pre versus Post processing

There is usually more than one way to accomplish almost everything so it’s worth taking a moment to consider two different ways to process and event log. Post-processing describes the case where you allow the tracing system to collect every event and then you worry about reducing your data set after you’ve collected the data. Support for this doesn’t really depend on the diagnostic system you use since you can post-process any log that you can get into a tool that supports filtering, for example Excel. Some diagnostic systems have built in tools to do this as well. Pre-processing is when the filtering of events happens as part of the event generation code and therefore can “short-circuit” the event from firing. Extended Events support this type of pre-processing and that is what a Predicate if for.

Pre-processing has some cost associated with evaluating the predicate which is why some diagnostic systems choose not to offer this functionality, ETW is an example of a diagnostic system that does not support pre-processing of events.

Aside: Some ETW gurus (I don’t claim to be one) may argue that ETW does support pre-processing because you have the ability to define which events are collected based on Channel, Keyword and Level. For our purposes I’m defining pre-processing as requiring a finer level of control than that; you can decide if this is a semantic point or not.

The obvious question is: why would you want to use predicates if there is a potential cost? There are a couple of reasons for using a predicate, and as always, whether it is worth it for you to use is going to depend on your situation. The first common reason you might want to use predicates is when the performance hit of collection is higher than the performance hit of evaluating the predicate. Extended Events supports the ability to collect additional data such as call stacks or memory dumps and these can be expensive operations. In order to reduce the performance impact of these expensive operations you can use a predicate to increase the specificity of your event collection. Another common reason to use a predicate is to reduce the volume of events being collected. Many events can occur at extremely high volume, particularly those in the Analytic and Debug channels, and result in “log flooding”, or the behavior of putting many thousands (hundreds of thousands?) of events into the log that are not related to the problem you’re actually troubleshooting. All this extra data makes it hard to analyze the real issue and can cause the log files to be large enough to cause a disk space problem. Again, the solution is to increase the specificity of your event collection which will reduce the number of events you are looking at. Here are a few ways that you might use a predicate to increase specificity of your event collection:

• Only collect events that occur within a specific database or session_id.
• Only collect a sampling of events from your system. (I’ve described sampling in this post.)

So, how much do I save?

So now you’re wondering what you’re getting for your trouble and for that we need to take a look at what is actually going on inside Extended Events when an event is fired. For our purposes you can think of firing an event as requiring five steps as shown below.

 

The predicate evaluation happens at step three, but don’t let the fact that this is the “middle step” fool you, the amount of work required to generate an event is not equally distributed across these steps. Take the earlier example of collecting a memory dump, this is an expensive operation that happens in step 4 and when combined with publishing the event to a target is easily more costly than collecting the event data itself. The ability to evaluate a predicate right after the minimum amount of event data is collected can significantly reduce the cost of event collection in the cases where you are looking for a specific set of data.

Aside: SQL Trace/SQL Profiler also support pre-processing through the use of a filter (sp_trace_setfilter) but the filter is not applied until the entire Event Class and associated data has been collected resulting in relatively little performance savings.

 

OK, so how do predicates work

We’re finally to the actual topic of this post – thanks for sticking with me through all the preliminary stuff…

Predicates in Extended Events are very similar to the WHERE clauses that you’ve probably used in T-SQL; they’re Boolean expressions that can be evaluated against two types of data:

• Local event data (Now you understand why we need to collect EventData first.)
• Global state

Local event data is any of the fields that are automatically collect when an event fires. You can examine the list of local data fields for any given event by running a query against one of the DMVs for Extended Events; for example this query shows the fields for the wait_info event.

SELECT name, type_name FROM sys.dm_xe_object_columns WHERE object_name = 'wait_info' and column_type = 'data'

Field Name Type wait_type wait_types opcode event_opcode duration uint64 max_duration uint64 total_duration uint64 signal_duration uint64 completed_count uint64

Global state represents data that is available in thread local storage (TLS) at all points in time in the SQL Server instances, or at least at most points in time. Examples of global state are things such as the session ID or the name of the client application. We call these predicate sources (pred_source for short) and you can query the list of predicate sources using a DMV.

SELECT name, description,
    (SELECT name FROM sys.dm_xe_packages WHERE guid = o.package_guid) package
FROM sys.dm_xe_objects o
WHERE object_type = 'pred_source'
ORDER BY name

If you’re familiar with Extended Events you’ll notice that there is a strong correlation between predicate sources and actions. This correlation is on purpose so that you can write predicates against the same set of global data that you can collect using actions. But don’t let the similarities in names trick you into believing these are the same thing; the difference is the timing. Think back to the steps involved in firing an event – the point where the predicate is analyzed is where we collect the pred_source if there is a predicate that uses it. It’s not until after a predicate has evaluated as True that the system collects action data. This is the kind of technical detail that will make you sound cool at conferences when you overhear someone say “I used a predicate on the session_id action.” and you can explain that the predicate does not, in fact, use the session_id action, but rather the session_id predicate source. You would then further explain how the predicate sources allow for the creation of predicates over global data that is similar to that in actions, but that it can be done without having to collect the actions, therefore improving performance by short circuiting the action collection on events that are not of interest.

Now that we have an understanding of where the data comes from, lets look at a few predicates; we’ll use the wait_info event described above:

Only collect the event if the duration is more than 500 milliseconds.	ADD EVENT wait_info (WHERE duration > 500)
Only collect the event for session 52.	ADD EVENT wait_info (WHERE sqlserver.session_id = 52)
Only collect the event for NETWORK_IO waits.	ADD EVENT wait_info (WHERE wait_type = 99)

You can infer a couple rules from this list which I’ll state explicitly:

1. You only need to use the field name when building a predicate on local event data. (duration > 500)
2. You need to use the package name when using a predicate source. (sqlserver.session_id = 52)
3. You need to pay Mike to learn the secret translation of specific wait types to the number that represents them.

The secret translation of wait types into numbers (Maps): OK, so I’m not going to make much money from this secret, oh well. In order to make collection of some data a bit more efficient we created something called maps which simply map an integer to a more friendly string value. You can recognize map fields as those with data types that don’t seem to match with any type you’ve seen before, for example “wait_types”. You can look up the integer to friendly name comparison in the dm_xe_map_values DMV and matching the field’s type to the name column in the DMV. So to get a list of the numeric representation of the various waits, use the following query: SELECT * FROM sys.dm_xe_map_values WHERE name = 'wait_types'

The final twist

Another long post but there is just one more thing to discuss before I bring it to a close and that is predicate comparators(pred_compare for short). As you’ve probably guessed from the name, predicate comparators are the operators in the Extended Events predicate system. We’ve done the work to map the standard mathematical operators (=, <, >, !=, <=, etc.) to the appropriate predicate comparators so that you don’t have to see the gory details. Under normal circumstances you probably won’t need to go beyond using these mapped operators and your predicates will look very similar to what you’d expect if you have a working knowledge of the WHERE clause in T-SQL. There may be an occasional need to walk on the wild side and do something out of the ordinary. For these times you have a whole list of interesting pred_comps to play with. You can see the list by querying a DMV:

SELECT name, description,
    (SELECT name FROM sys.dm_xe_packages WHERE guid = o.package_guid) package
FROM sys.dm_xe_objects o
WHERE object_type = 'pred_compare'
ORDER BY name

As you scan down the list you’ll see that most pred_compare operators have obvious mappings to a mathematical operator for a specific data type, for example, greater_than_int64 is mapped to > for 64 bit integers. If you look closely you will find a small number that don’t have an obvious mathematical equivalent, such as greater_than_max_int64 or less_than_min_int64. These pred_compare operators are special in that they do more than a simple comparison, in fact, in the case of the two mentioned, they actually maintain state within the predicate and use that stat for the comparison. Let’s consider greater_than_max_int64: this pred_compare will maintain the maximum value of the field passed to it and only fire when it encounters a value that is greater than the current maximum, at which point it sets a new max value and waits until the next time it is exceeded. You might find this useful if you have a statement that is degrading over time and you only want to capture the event data at the points that a degradation actually happens in order to examine what else is happening at those times. Sure, you could capture all the query events and draw a graph to find the points of degradation, but this pred_compare does it for you automatically.

Calling a pred_compare directly follows this syntax:

package_name.pred_compare(field/pred_source, value)

Lets look at the duration predicate described before, but this time collect only when the duration exceeds 500 and is larger than the last maximum duration:

ADD EVENT wait_info (WHERE package0.greater_than_max_int64(duration, 500)

Wrapping it all up

Despite my single criteria example, you can actually build a predicate statement with multiple criteria using the common AND/OR syntax. Order is important and you should put your most specific criteria first because the the event will short circuit as soon as it hits the first part of the predicate that makes it false. So lets pull the entire post together with a predicate that will only return events for the NETWORK_IO wait type when it occurs on sessions with an ID of 52 and only when the duration is larger than the max duration but never less than 500 milliseconds. (I bet you knew that would be the example.)

ADD EVENT wait_info
(WHERE wait_type = 99 AND sqlserver.session_id = 52 AND
package0.greater_than_max_int64(duration, 500))

Whew! Hopefully that gives you an idea how Predicates can help you create very specific event sessions and even do some interesting analysis directly in the session. Let me know if there is a topic you’d like to see covered in this blog and we’ll add your request to the topic list.

- Mike

Comments

• Anonymous
  June 12, 2014
  Thanks for posting this article. I've been trying to find a good example of LIKE predicates online, and this is the first article I found that actually helped. I appreciate it.