August 13, 2020 at 11:46 am
Hello,
I am reading Stellato's Extended Events Stairway series and in the final (4th) level, she talks about how important predicate order is in regards to short-circuiting. I understand that you want to put the predicates that will evaluate to false earlier in the predicate expression so that evaluation stops earlier and control is passed back to the task execution thread quicker.
But I can't quite grasp the following below where she states.....
"Predicate order is important because the earlier a predicate evaluates to false, and thus short-circuits, the less work the engine has to do, and the lower the overhead. If the majority of my statements take less than 1 second, then it may be appropriate to put duration first in the predicate definition, because the majority of events will immediately evaluate to false.
In the previous example, we defined predicates on event fields that were part of the default payload for the event. However, we can also define predicates for global fields. In this case the XE engine has to collect that additional data in order to evaluate the predicate condition. It executes the collection synchronously, on the task execution thread, as discussed previously. Having captured the data, it evaluates the predicate. If it evaluates to false, then the event data collection terminates. If it evaluates to true, then any actions are executed and the event data is dispatched to either its target or to the intermediate memory buffers.
We can illustrate this further with the sample code in Listing 8.
CREATE EVENT SESSION [MyEventSession] ON SERVER
ADD EVENT sqlserver.sp_statement_completed ( SET collect_object_name = ( 1 ) ,
collect_statement = ( 1 )
ACTION ( sqlserver.client_app_name, sqlserver.database_name )
WHERE ( [sqlserver].[database_id] = ( 7 )
AND [logical_reads] >= ( 10000 )
AND [duration] >= ( 1000 )
) );
GO
Listing 8
The database_id action is first, so the associated condition will be evaluated first. The XE engine collects database_id data column and event firing only continues if that value is 7. If most of my statements do not come from database_id of 7, having this predicate evaluated first is not most efficient."
My question is, so why is this not most efficient and what would be more efficient? If most of my queries are not executed from database_id if 7, the predicate will evaluate to FALSE immediately and won't control pass back faster?
Is it because logical reads and duration actions have already been collected and having database_id first makes the XE engine collect ALL non default payload actions. This is confusing to me... thanks for any clarification.
-Jason
August 14, 2020 at 12:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply