As those of you who managed to get to the bottom of my recent deadlocking post may have seen I am busy writing a book with Christian Bolton, Justin Langford, Brent Ozar, Steve Wort, Cindy Gross and Jonathan Kehayias. Right now I am working on the SQL Trace and Profiler chapter and am currently describing the catalog views available for SQL Trace. Most of these are reasonably straight forward but there were a couple of columns on sys.traces_columns that piqued my interest.
I was looking at Books Online for some inspiration as I wasn’t sure what the is_repeatable and is_repeated_base columns meant. You can view the article for yourself here http://msdn.microsoft.com/en-us/library/ms180067.aspx. Unfortunately I didn’t really get an answer from BOL. The columns and their BOL descriptions can be found below.
| Column_name | Data Type | Description |
| is_repeatable | bit | Indicates whether the column can be referenced in the “repeated column” data |
| is_repeated_base | bit | Indicates whether this column is used as a unique key for referencing repeated data |
As I say, I didn’t find myself any closer to knowing what these columns indicated. I thought I’d have a look at the data to see what the columns had these bit values set to see if that shed any light on proceedings.
SELECT trace_column_id
,name
,type_name
,max_size
,is_filterable
,is_repeatable
,is_repeated_base
FROM sys.trace_columns
WHERE is_repeatable = 1
OR is_repeated_base = 1
Returned
Hmm nothing on the face of it. Nothing that described what was meant by “repeated data”. Time to Bing! it. Ta Da! Whaddya know the answer lies at the US Patent Office. My search uncovered this article http://www.patents.com/System-monitoring-performance-a-server/US7155641/en-US/. It’s the patent assignment to Microsoft for SQL Trace. Actually, it’s a great read and I thoroughly recommend you download the pdf so you get to see the drawings in conjunction with the available text. However, for the purposes of “repeated data” the bits you want to read are these.
The "trace repeated data" column indicates a desired repetition of data from a preceding event. The "trace repeated data" column is preferably followed by a range of column identifiers in which to insert the repeated data. Such repetition is available only for columns specifically defined as repeatable columns.
…
Repeatable element 630 provides an indication of whether column 610 is repeatable. Server 220 desirably need not resend data corresponding to a repeatable column in a subsequent trace. If a repeatable column is selected to be repeated, then it is indicated in the "trace repeated data" trace special column set forth above.
So there we have it. The metadata for repeatable columns has been exposed in the sys.traces_columns catalog view. It means that the columns marked as is_repeatable can derive their values from earlier events with the same SPID. I can say this because SPID is the only column to have the is_repeated_base flag set to true. If we look at the columns that are marked as is_repeatable this all begins to make sense. The data we’d expect to see in repeatable columns are static in nature and so it makes sense to try and optimise the trace by deriving this event info from a previous event rather than re-send this additional column data.
What does this all mean? Well, to me it means that we should be able to add these columns to our trace without fear of it causing performance degradation. That’s a theory of course. However, now I am going to see how I can work this into my chapter….
Cheers, James