Tuesday, February 22, 2022

SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions

Moved from: bobsql.com


You need to make sure to order the events by event_sequence when doing analytics on the XEvent data. It has been proposed to me that you can use the causality tracking sequence as a sort target, which may work as long as you understand how the activity ids are associated with the parent and the sequence is produced.



  • Values are generated when the event sequence is submitted to the target
  • Values are always incremented
  • The event_sequence is unique to the session
  • The activity id is unique to the task with xfer reference to the parent task

You can sort

  • The session by event_sequence
  • Individual task by attach_activity_id.seq
  • Use the attach_activity_id_xfer.seq to locate the tasks associated with it. However, the child task ordering within a parent sequence requires event_sequence

Causality tracking

The sequence value associated with causality tracking is incremented when the event is recorded. There are two forms of activity ids.

  • attach_activity_id is a local id associated with the worker. This is incremented using ++ as each event is produced.
  • attach_activity_id_xfer is an activity id of the parent, usually associated with the task when the task begins execution (SOS_Task::Param::Execute)

00 sqldk!XE_Tls::Record::GetActivityId
01 sqldk!XE_Session::Submit
02 sqldk!XE_Engine::EventFireEnd
03 sqllang!GenericEvent::FireEnd
04 sqllang!GenericEvent::CallNextAction
05 sqldk!XE_AppendActionData
06 sqldk!XEPackage0::GenerateEventSequenceNumber::Invoke
07 sqllang!XE_AppendActionData
08 sqllang!XeSqlPkg::CollectSessionIdActionInvoke
09 sqllang!GenericEvent::CallNextAction
0a sqllang!XeSqlPkg::logout::Publish
0b sqllang!CSecSpecialAudit::ProduceLogoutExtendedEvent
0c sqllang!CSecSpecialAudit::AuditEvent
0d sqllang!AuditCheckSpecialAction
0e sqllang!TraceLogoutEvent
0f sqllang!CSession::LogoutSession
10 sqllang!CSession::RemoveBatch
11 sqllang!process_close_connection

Event Sequence

The event_sequence uses InterlockedIncrement making the value unique to the events produced for the session. The sequence value is tracked as part of the action object associated with the session meaning you get different sequence values for different sessions.


00 sqldk!XEPackage0::GenerateEventSequenceNumber::Invoke
01 sqllang!XE_AppendActionData
02 sqllang!XeSqlPkg::CollectSessionIdActionInvoke
03 sqllang!GenericEvent::CallNextAction
04 sqllang!XeSqlPkg::logout::Publish
05 sqllang!CSecSpecialAudit::ProduceLogoutExtendedEvent
06 sqllang!CSecSpecialAudit::AuditEvent
07 sqllang!AuditCheckSpecialAction
08 sqllang!TraceLogoutEvent
09 sqllang!CSession::LogoutSession
0a sqllang!CSession::RemoveBatch
0b sqllang!process_close_connection

If you look at 2 sessions, tracking the same events, the event_sequence values are uniquely, incrementing for each session.


Posted at https://sl.advdat.com/3JJqU9vhttps://sl.advdat.com/3JJqU9v