Comparison Predicates and Event Data

In the previous installment, I began covering the topic of predicates. In that article I demonstrated that there are multiple types of predicates. There are two objects that fall into the predicate category and then there are data comparison predicates (those that are not objects).

The two objects are pred_compare and pred_source. With pred_source having been covered in that first part, I will now cover the pred_compare object as well as the standard predicate that is not an object.

In that previous installment, I associated predicate to a filter. I also mentioned that a predicate in Extended Events is a means to short circuit event evaluation. I want to underscore the importance of that feature.

If you imagine a complex conveyor belt system as if it were the Extended Event engine, you might see something like this.

Packages roll along the conveyor system and will reach various check-points. At each check-point, a bar code is scanned and the package is moved merrily along its way.

There can be many paths for a package as it rolls along from start to finish within a network of conveyor belts.

Now, focus on the scanner as I have done with the next image.

This scanner is the filtering mechanism. If the bar code of the package meets the requirements to be rerouted along a different belt, this scanner will be able to make that happen. The package will be scanned, meet the redirect requirements, and then the scanner flips a mechanical switch to redirect the package.

This is not too different from Extended Events. When the payload of the event (package) meets the predicate (scanner) requirements, the payload is sent down a path to a pre-defined target. If it doesn’t meet those requirements, the event just passes by as if nothing happened (other than being scanned).

Now consider the predecessor (e.g. the method employed by Profiler/SQL Trace). The filter mechanism routed everything to the same spot and then attempted to apply the filter after the collection was made. The new filter process, employed by Extended Events, is many times more efficient and less impacting than the method employed by Profiler and SQL Trace.

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:

-- Comparison Predicates
SELECT p.name AS package_name
, o.name AS predcomp_name
, o.description
FROM sys.dm_xe_objects AS o
JOIN sys.dm_xe_packages AS p
ON o.package_guid = p.guid
WHERE ( p.capabilities IS NULL
OR p.capabilities & 1 = 0
)
AND ( o.capabilities IS NULL
OR o.capabilities & 1 = 0
)
AND o.object_type = 'pred_compare';

Exploring through this list of predicates, one will see 77 different compare predicates. Here is an abridged example of that list.

Reading through the list, one could quickly figure out that the less_than* and greater_than* comparators would map to the < and > operators respectively. Despite the easy mapping to the overloaded counterparts of the operators, the way to use these is slightly different than with good old TSQL syntax. One needs to think more programmatically to utilize these types of objects.

Suppose I wanted to use one of these objects when trying to find all of the stored procedures taking more than 500 milliseconds. I would need to write my predicate like the following:

ADD EVENT sp_statement_completed
(WHERE package0.greater_than_max_int64(duration, 500000)

The alternative (and more common approach) would be:

ADD EVENT sp_statement_completed
(WHERE duration > 500000)

Now suppose that I go ahead and use the easy syntax (TSQL syntax) to create a predicate on the sp_statement_completed event and deployed that session to a server. The event engine will take that overloaded syntax and do a translation for me. The syntax that it will produce would be of the following nature:

In this particular example, I chose to have two predicates. The two are a bit different, and the deeper explanation will be discussed in the future. The simple explanation for the difference is that one is based on an Action and the other is based on an event. Looking at the predicate within the green box, one will see that the sp_statement_completed event has a predicate on it that uses the greater_than_uint64. Then the field that is being compared is added within this XML. The last piece of this leaf node is the value. I decided to go for 25 seconds of run-time instead of 500 ms. (Note the values here are in microseconds and not milliseconds.)

All of that is generated from the predicate I wrote as “duration > 25000000” when creating the event. Whether using the more familiar TSQL syntax of the XE syntax to build your compare predicates, it will be translated and stored in an XML format using the actual pred_compare objects. Keep this in mind when building predicates.

Earlier, I stated there are two types of predicates to discuss in this segment. The examples I just showed are an illustration of those two types of predicates. Call it standard TSQL syntax vs. the XE syntax for the comparison operators within a predicate. That’s really what it boils down to.

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