1

I have been looking into using Extended Events in a full end-to-end tracing scenario. I am especially interested in the XEvent's activity id that helps follow event execution sequence for a given activity.

You will have guessed, my event session in SQL Server 2008 has the following option set:

TRACK_CAUSALITY = ON

My question to you is, when executing SQL queries from C# code (Linq, Entity Framework, etc...), is it possible to infer the activity id that will be traced by the extended events in SQL Server?

For instance, I would like to be able to propagate the CorrelationManager.ActivityId guid of my executing thread to SQL Server for XEvent tracing.

Many thanks

Talisker
  • 177
  • 2
  • 16

1 Answers1

2

Extended Events doesn't track the activity_id for anything outside of SQL Server currently. TRACK_CAUSALITY is only for tracking what event in SQL Server lead to another event in SQL Server, for example a sql_statement_starting event would have a specific activity_id and seq number 1, then a subsequent file_read event would have the same activity_id and a seq of 2, a wait_info event for PAGEIOLATCH_SH would have the same activity_id and seq of 3, and so on until you got to sql_statement_completed with a final seq number for the activity_id. You find plenty of examples showing this on my blog posts about Extended Events:

http://sqlskills.com/blogs/jonathan/category/Extended-Events.aspx

If you dumped the data into the ETW target, you can merge it with an ETW trace of your application and draw loose correlations of the events based on occurence, but there is causality tracking between the two possible.

Jonathan Kehayias
  • 3,402
  • 1
  • 23
  • 23
  • Thank you Jonathan, once again, you have been very helpful and very clear in your answer. – Talisker Sep 28 '11 at 17:01
  • Yes, sorry I didn't have a better answer for this, but one doesn't exist at the current time. One would hope that the engine team will change the ETW provider for XEvents away from classic and into the manifest based provider so that it could better integrate with ETW in Windows which might allow you to then do this level of work. – Jonathan Kehayias Oct 05 '11 at 16:48
  • 2
    Just an update on this, in SQL Server 2012, you can pass the ID from the SQL Native Client from the application through to do end to end tracing. This is documented in the following BOL Topic: http://msdn.microsoft.com/en-us/library/hh213095.aspx – Jonathan Kehayias Nov 07 '12 at 21:55
  • Wow, this is a very interesting addition, thanks for the update Jonathan – Talisker Nov 19 '12 at 17:15