Extended Events - Remove data from Event output that is available by Actions - by Jonathan Kehayias

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 639818 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 1/28/2011 6:22:55 AM
Access Restriction Public


The sql_statement_starting and sp_statement_starting events in SQL Server 2008 Extended Events have offset information in the basic data output from the event.  This information is already available to any event in the sqlserver.tsql_stack Action, and should not be carried on the Event like this as it is not necessary and it is not useful without the plan_handle since the sql_text is not parsable with offset information.

In Denali CTP1, this information was expanded into sql_statement_starting, sql_statement_completed, sp_statement_starting, 
sp_statement_completed, and sql_statement_recompile while it is still available in the tsql_stack.  This bloats the Event size and this information is not even need because the statement column is customizable and does not require offset parsing to get the firing statement text.
Sign in to post a comment.
Posted by Jonathan Kehayias on 1/31/2011 at 4:34 PM
The cost of raw storage in the target may only be 2 int32 values, but that is not how Extended Events data is consumable. The cost to the XML Event Data output that we have to query through a system function in the Database Engine currently is significantly more than 2 int32 values. In fact it is significantly more than just 8 bytes and since the biggest problem with performance related to Extended Events is dealing with the XML Event Data that currently has to be queried through the DB Engine and streamed back as XML which is much heavier, this is something that should be considered.
Posted by Microsoft on 1/31/2011 at 12:55 PM
After speaking with the Extended Events team, our recommendation would be to leave the offset fields as they are. While it is true that they represent a duplication of data with the tsql_stack action we feel that there are use cases for collecting just the core events, without the tsql_stack, in which the offsets add value. Basically this is a discussion about the trade-off between the size of the EventData compared to the cost of collecting Actions. The XEvent is stored in binary so the size differential here is 2 x int32 values, so the extra flexibility gained outweighs the storage cost. A contributing factor, but not necessarily decisive in this case, is that the schema for these events were designed to be comparable with the existing SQL Trace Event Classes that they replace.