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-circuiting. 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:

SELECT DISTINCT xo.object_type
FROM sys.dm_xe_objects xo
WHERE xo.object_type LIKE 'pred%';

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.

SELECT xp.name AS package_name
, xo.name AS predicate_name
, xo.description
FROM sys.dm_xe_objects AS xo
INNER JOIN sys.dm_xe_packages AS xp
ON xo.package_guid = xp.guid
WHERE ISNULL(xo.capabilities,0) & 1 = 0
AND xo.object_type = 'pred_source'
;

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:

SELECT ISNULL(pred.PackageName,act.PackageName) AS PackageName
, pred.PredName
, act.ActionName
, pred.PredDescription
, act.ActDescription
FROM (
SELECT xo.name AS PredName
,xo.description AS PredDescription
,xp.name AS PackageName
FROM sys.dm_xe_objects AS xo
INNER JOIN sys.dm_xe_packages AS xp
ON xo.package_guid = xp.guid
WHERE ISNULL(xo.capabilities,0) & 1 = 0
AND ISNULL(xp.capabilities,0) & 1 = 0
AND xo.object_type = 'pred_source'
) pred
FULL OUTER JOIN (
SELECT xo.name AS ActionName
,xo.description AS ActDescription
,xp.name AS PackageName
FROM sys.dm_xe_objects AS xo
INNER JOIN sys.dm_xe_packages AS xp
ON xo.package_guid = xp.guid
WHERE ISNULL(xo.capabilities,0) & 1 = 0
AND ISNULL(xp.capabilities,0) & 1 = 0
AND xo.object_type = 'action'
) act
ON pred.PredName = act.ActionName
AND act.PackageName = pred.PackageName
ORDER BY PackageName,pred.PredName,act.ActionName
;

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.