Extended Event File Target size vs SQL Server Trace trace file - a comparison

No big science, more out of curiosity this time..

The Extended Events File Target for SQL Server saves data using xml, which as is well known, is a bit “chatty”. A student in my recent SQL Server Master-Class workshop on extended events came up with the question for how much (more) space he would have to account for using Extended Events with a file target. Although this depends greatly on the specific events and possibly actions, selected, I was a bit curious myself and decided for a small test.

Both, the old and deprecated SQL Server Trace and Extended Events can save the data in a file, so it’s easy to compare what difference in size the new format will make.

I set up a SQL Server Trace that is almost identical to an Extended Events Trace. (You will see why “almost”.)

I had to choose a very simple Trace, so the customizable columns of extended events would not make the comparison unequal and ended up with a trace that captures SP:Starting/SP:Completed with the following columns:

You will see why I collect Source/DatabaseID twice later on.
Of course I used a lightweight Server-Trace, although for the purpose of this comparison it would not have mattered.

The SQL Trace definition:

exec sp_trace_setevent@TraceID, 43, 3, @on
exec
sp_trace_setevent@TraceID, 43, 5, @on
exec
sp_trace_setevent@TraceID, 43, 12, @on
exec
sp_trace_setevent@TraceID, 43, 13, @on
exec
sp_trace_setevent@TraceID, 43, 22, @on
exec
sp_trace_setevent@TraceID, 43, 28, @on
exec
sp_trace_setevent@TraceID, 43, 34, @on
exec
sp_trace_setevent@TraceID, 43, 48, @on
exec
sp_trace_setevent@TraceID, 43, 62, @on
exec
sp_trace_setevent@TraceID, 42, 3, @on
exec
sp_trace_setevent@TraceID, 42, 5, @on
exec
sp_trace_setevent@TraceID, 42, 12, @on
exec
sp_trace_setevent@TraceID, 42, 22, @on
exec
sp_trace_setevent@TraceID, 42, 28, @on
exec
sp_trace_setevent@TraceID, 42, 34, @on
exec
sp_trace_setevent@TraceID, 42, 62, @on 

declare @intfilter int
declare
@bigintfilter bigint

 set @intfilter = 7

exec sp_trace_setfilter@TraceID, 62, 0, 0, @intfilter

As you might see the trace includes a filter, which is for a specific database ID.

The Extended Event Trace session looks like that:

CREATE EVENT SESSION [ModulesStartEnd_ToFile]
ON
SERVER
ADD
EVENT sqlserver.module_start(
    WHERE ([source_database_id]=(7))),

ADD
EVENT sqlserver.module_end(
    WHERE ([source_database_id]=(7)))

ADD
TARGET package0.event_file
   
(SET filename=N'R:\Tracing\XE\ModulesStartEnd.xel', max_file_size=(10240))
WITH
(MAX_MEMORY=
4096 KB, EVENT_RETENTION_MODE=NO_EVENT_LOSS,  MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)

You may know, that Extended Events include certain columns by default, and for module_start/end, this includes offset and offset_end.
 

Those two columns are not available for SP_Staring/SP:Completed in SQL Trace. Since they are both integers, I decided to include another column, DatabaseID, into the SQLTrace. SQL Trace also includes the SPID by default, which cannot be deselected, therefore those two columns should equalize it.
Both traces were started before the workload which ran for a while. At the end, the same number of events have been logged by both technologies in parallel.

SQL Trace event count:

XEvent Trace event count:



100644 + 100644 = 201288, so both captured the exact same events. :-)

So, and now to the final question: what size are the files?

See yourself:

Size in Megabytes:


 
(The numbers in MB are the real size, whereas windows explorer shows the size on disk.)
That’s a difference of 5.32MB or in other words 29.13%.

And this is what one single module_start-event for a function call in XEvents looks like:

<eventname="module_start"package="sqlserver"timestamp="2013-06-08T18:41:48.780Z">
<
dataname="source_database_id"><value>7</value></data>
<
dataname="object_id"><value>103671417</value></data>
<
dataname="line_number"><value>1</value></data>
<
dataname="offset"><value>0</value></data>
<
dataname="offset_end"><value>-1</value></data>
<
dataname="object_type"><value><![CDATA[TF]]></value></data>
<
dataname="object_name"><value><![CDATA[ufnGetContactInformation]]></value></data>
<dataname="statement"><value></value></data>
</
event>

The content is self-explanatory, as xml is supposed to be, and the overhead in size is no surprise at all.

Keep in mind that this post is purely on comparing file sizes, and not performance or features. There are good reasons that SQL Trace & Profiler have been deprecated, and Extended Events in SQL Server 2012 overcomes SQL Trace & Profiler by far, in performance as well as in flexibility/usability.
For a performance overhead comparision check out my recently published benchmark blog post: "Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load".

So whenever performance matters, remember to set session options appropriately and if the amount of events is high, do not use your slowest volume for the file target - same as for all other tracing activities anyways.

happy tracing,

Andreas

  • Ashish
    Comment from: Ashish
    2015-10-15 @ 15:11:48

    Hi Sir, currently i am working on the enhancement for MS SQL for event monitoring. The requirement is that We used to monitor events from the SQL server trace initially. Now we have to migrate to Extended event file(.xel). we use to query trace table to get the event data. There it used to keep the track of the row number in order to fetch the data next time after that row. Since Extended events are in file i cant use the same query to get the data from the file. I have query that get data from the file but there is no field that tell me that i have already read this data(like column number from table,there i can query WHERE column > x). can you please tell me what field i can use for this. there is one column called file_offset but i dont think i can use it. Thanks.

  • Comment from: Andreas Wolter
    2015-10-17 @ 14:31:57

    Hello Ashish, file_offset should be correct to use. I haven't tested it lately though and I know in the beginning there was a bug. But that should be fixed, so I would that that. What I do for my deadlock-collector, is having a hash over a couple of columns + of course the event date and time.

  • Ashish
    Comment from: Ashish
    2015-10-20 @ 12:05:36

    Hello Andreas Wolter, Thank you very much for the reply.I tried using the file_offset and the event date and time and it worked.

Leave a comment

Your email address will not be revealed on this site.

Your URL will be displayed.
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
Bitte geben Sie den Namen dieser Domäne ohne http://www., aber mit Endung ein?
Please answer the question above.
  • Torsten Schuessler
    Trackback from: Torsten Schuessler
    2013-06-13 @ 06:30:54

    SQL Server 2012 Tempdb File Size Change tracked with Extended Events
    We are using SQL Server 2012 and the tempdb is growing so fast. How do I effectively find what’s causing my tempdb growing. Can I monitor the file size change with Extended Events to find out the ...