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