The Importance of the Trace Event Sequence Column and SQL Server 2005 SP2 Changes

Many of us are used to looking at a SQL Trace and making the assumption that the order of display is what occurred during the capture.  

SQL Server 2005 introduced a new column named ‘Event Sequence’ that you should capture for any trace events.  The Event Sequence is the order the trace event was internally created in the server which can be different from the physical file order due to various timing and design issues.   The Event Sequence gives you a more accurate representation of the event order.  When looking at trace data from SQL Profiler or one loaded in the database you should order by the Event Sequence.

SQL Server 2005 shipped with an Event Sequence stored in a LONG.   Support has seen a few traces where the bounds of the signed long are exceeded and the Event Sequence becomes negative.  The SQL Server is using an interlocked instruction to increment the value so the event sequence counts down negatively until wrap around to 0 occurs.  The pattern becomes the following.

Step 1:  Count up from 0 to 0x7FFFFFFFF
Step 2:  Count down from 0x7FFFFFFF to 0

Start at step 1 again.

If you load a trace containing negative event sequences and then perform the order by operation the physical order will be revered showing the last event in the trace as the first event.  You also can’t load the trace into a table and cast the data as a BIGINT.   SQL Server does not have unsigned data types so the negative value is just propagated to a negative 64 bit integer instead of a 32 bit integer.

This is a very rare condition for most systems because to produce 2 billion+ trace events does take quite a bit of tracing to say the least.  

SQL Server 2005 SP2 expands the column to a LONGLONG (signed 64 bit integer) which would require more than a little bit of tracing to cause a wraparound condition.

- Bob Dorr

Comments (2)

  1. Dan Holmes says:

    If event sequence is *the* way to order the rows, what happens when the EventSequence value is NULL?  That appears to be the case with 'Audit Login', RPC:Completed, SQL:BatchStarting and SQL:BatchCompleted events.  With out that sequence number you can't bookend the events between the started and completed since i can't trust the StartTime.

    I know this is an old post but i couldn't find an answer elsewhere.  thanks

  2. Abhishek Mehta says:

    Hey Dan,

    Did you happen to get an answer. I am also facing the same issue of having event sequence=NULL.

    Any takers?

Skip to main content