How to consume attach_activity_id and attach_activity_id_xfer columns in extended events

GokhanVarol 2013-03-22 14:50:29

I could not find a documentation on showing how this columns are linking the rows together. I noticed in my extended event session attach_activity_id does not have nulls but attach_activity_id_xfer columns in extended events is mostly nulls. Both have a sequence looking number in brackets ([0] , [24] etc) at the end of them which seems like the sequence they got fired.
I have a large dataset with *_completed (rpc_completed,
sp_statement_completed, …) events and query_post_execution_showplan event but I am not sure how I can link them to each other with sql statement.
Is there an example or documentation showing how to use these fields?
Thank you
Jonathan Kehayias 2013-03-22 15:50:06
I'm not sure what this question has to do with SQL Performance or why you would ask it here instead of on the other more appropriate forums that are more general in nature and not intended to be for SQL Server Performance issues and Execution Plan assistance.

One can only hope that the event session that you describe above isn't actually running on a production server because your selection of events would definitely affect the performance of the system, specifically query_post_execution_showplan which can have a 20-30% degradation of performance depending on your workload characteristics.

To answer the question, an attach_activity_id_xfer only gets generated when a parent task hands off to a child task and the activity_id changes from the original to a new transfered id. The event making the hand off will have the attach_activity_id_xfer and then every subsequent event will have the new activity_id as the attach_activity_id and the id_xfer will be NULL. Tracking this and parent/child relations between events when this occurs is entirely manual.

GokhanVarol 2013-03-22 16:36:03
I am collection the query filters for a specific application name and login. Will this impact the rest of the server?
Jonathan Kehayias 2013-03-22 17:11:53
Absolutely! You'll see this in my article on this event and why it shouldn't be used sometime next week (already written and pending publish) when it publishes. You should really test the impact of Extended Events sessions before using them in production, or at a bare minimum monitor performance counters before and after starting a session to monitor its effect.

The description of that event says "Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time." There are only 4 events out of 625 in Extended Events that have that in their description and there is a reason why it is there.

GokhanVarol 2013-03-22 17:16:06
I misunderstood what a predicate means, I thought Application Name, LoginName etc can be evaluated at the very beginning.
I am waiting for the article you mentioned.
Thank you
Jonathan Kehayias 2013-03-22 17:41:44
The Event Execution Lifecycle is covered in detail in the Extended Events whitepaper I wrote on MSDN. A simplified version is:

The event checks if it is enabled, if it is it collects all of it's column data, then it evaluates predicates to see if it fires, then if it passes predicate evaluation it executes any actions and writes the resultingdata to the sync targets immediately, then it buffers the data in memory for async targets.

There's a critical step before predicate evaluation that can significantly affect performance, data collection of the event columns. Getting the plan XML is not a cheap operation for a single query, now you just made the server do it for every statement executing on the server, even if the event will be discarded after the predicate evaluates.

GokhanVarol 2013-03-22 19:49:00
Thank you Johathan