Convert Existing SQL Server Traces to Extended Events Sessions

Problem

While we don’t know exactly when SQL Trace will stop working, we do know that it is deprecated and that, at some point, you will need to switch over to Extended Events. Arshad Ali has already provided a great tip on Getting Started with Extended Events, but if you have a lot of traces in your environment, changing them all to Extended Events session by hand is going to be a nightmare.

Solution

Microsoft has an article that sounds promising, entitled, “Convert an Existing SQL Trace Script to an Extended Events Session.” But if you look at step 4, you can see that, beyond discovery, this is still going to be a largely manual process:

4. Manually create an Extended Events session …

Thankfully, Jonathan Kehayias of SQLskills has created a procedure that can automate this process for you, as long as you are on SQL Server 2012 or newer. (In SQL Server 2008 and 2008 R2, Extended Events did not have anywhere near full parity with SQL Trace, so it is unlikely that many of your traces could be directly converted.) I’m not going to repeat the procedure code here, since it may change over time; you can grab the current version directly from his blog post:

Converting SQL Trace to Extended Events in SQL Server 2012

I implemented this procedure on my system, then used the example query to generate an Extended Events session that was roughly equivalent to the default trace:

EXEC master.dbo.sp_SQLskills_ConvertTraceToExtendedEvents 
@TraceID     = 1, 
@SessionName = 'XE_Default_Trace', 
@PrintOutput = 1, 
@Execute     = 1;

This creates the Extended Events session, but it doesn’t actually start it. You need to add the following:

ALTER EVENT SESSION XE_Default_Trace
ON SERVER STATE = START;

(For the default trace, you’ll notice that several server-level events, like changing a database owner, still do not have an Extended Events equivalent. So for some of those events, you’ll need to find other ways to capture them – such as SQL Server Audit. Admittedly, Extended Events was not intended to be a direct 1-for-1 replacement for SQL Trace, but it should come pretty close in a lot of cases.)

Now, I wanted to see that the new Extended Events session I created was capturing the same kind of data as the default trace, so I ran this batch for sanity checking:

PRINT 'Testing object_created.';
EXEC sp_executesql N'CREATE TABLE dbo.whatever(id INT);';
GO
RAISERROR('testing errorlog_written event', 16, 1) WITH LOG;

Then I searched both the default trace file and the session target file for those events:

SET NOCOUNT ON;
DECLARE 
  @path NVARCHAR(260),
  @utcOffset INT = DATEDIFF(MINUTE, SYSDATETIME(), SYSUTCDATETIME());
SELECT 
  @path = REVERSE(SUBSTRING(REVERSE([path]), 
          CHARINDEX(N'\', REVERSE([path])), 260)) + N'log.trc'
FROM sys.traces
WHERE is_default = 1;
SELECT 
  StartTime = DATEADD(MINUTE, @utcOffset, StartTime), 
  ObjectName, 
  TextData 
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE 
  (EventClass = 46 -- Object:Created
   AND EventSubClass = 1 -- ddl_phase: commit
   AND ObjectName = N'whatever')
  OR 
  (TextData LIKE N'%errorlog'+N'_written%');
DECLARE @xepath NVARCHAR(260);
SET @xepath = N'XE_Default_Trace*.xel';
SELECT 
  StartTime = d.value(N'(/event/@timestamp)[1]', N'datetime'),
  ObjectName = d.value(N'(/event/data[@name="object_name"]/value)[1]', N'nvarchar(128)'),
  TextData = d.value(N'(/event/data[@name="message"]/value)[1]', N'varchar(max)')
FROM
(
  SELECT CONVERT(XML, event_data) 
    FROM sys.fn_xe_file_target_read_file(@xepath, NULL, NULL, NULL)
    WHERE object_name IN (N'object_created', N'errorlog_written')
) AS x(d)
WHERE 
(
  d.value(N'(/event/data[@name="object_name"]/value)[1]', N'nvarchar(128)') = N'whatever'
  AND d.value(N'(/event/data[@name="ddl_phase"]/value)[1]', N'int') = 1  -- commit
)
OR d.value(N'(/event/@name)[1]', N'nvarchar(128)') = N'errorlog_written';

And sure enough, I found them both, though what they stored was slightly different – the default trace captured the actual command that was issued to SQL Server, while the Extended Events session reported what was ultimately placed in SQL Server’s error log (and truncated it):

Events captured by default trace and equivalent Extended Events session

I will admit that querying fn_xe_file_target_read_file can be a bit more tedious than dealing with the output of fn_trace_gettable, and it can be a lot slower (a lot of people will dump the XML output into a separate #temp table before running XQuery against it). Also, I could have used Profiler and “Watch Live Data” to look at the trace and session data in real time, but you shouldn’t really be getting into that habit, because it will inevitably translate to usage in production scenarios, and that could be disastrous.

It is important to note that whether you continue using SQL Trace or you convert to Extended Events, there will always be some impact to the monitored server, often called “the observer effect.” In a different article, Jonathan demonstrated that an Extended Events session had less observer impact than an equivalent server-side trace, so in addition to the deprecation aspect, performance can also be a motivational factor to move from trace to Extended Events.

Next Steps

Start investing in a plan to convert your server-side traces to Extended Events.
Install Jonathan Kehayias’ procedure to help assess and perform that conversion.
See these tips and other resources:

Resources

Community

Subscribe