How to consume attach_activity_id and attach_activity_id_xfer columns in extended events
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?
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.