Predicates and Event Data

, 2015-09-21

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:

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads