Mapping Events from Trace to Extended Events

Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation.  It’s not always intentional, and I don’t know if it’s because I just can’t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added a query that helps you see what event in Extended Events is comparable to the event you’re used to using in Trace/Profiler.  While most of the events in XE have a name similar to the event in Trace (e.g. sp_statement_completed for SP:StmtCompleted), some mappings aren’t so intuitive.  For example, SP:Starting in Trace is module_start in XE, and SP:Completed in Trace is module_end in XE.  That’s not so bad, but if you’re ever monitoring file growths, note that the database_file_size_change event in XE is the event for the following four events in trace: Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, and Log File Auto Shrink.

(Note: if you happen to notice databases_data_file_size_changed or databases_log_file_size_changed in the list of XE, do not use either event.  Both are deprecated and won’t return any information.  Use database_file_size_change.)

Anyway, those fun one-offs aside, you’ll see that the query and its output are pretty straightforward:

SELECT 
	[t].[trace_event_id] [TraceEventID], 
	[t].[name] [Trace_Event], 
	CASE	
		WHEN [t].[name] LIKE '%Audit%' AND [xe].[xe_event_name] IS NULL THEN 'This event is not implemented in Extended Events, it may be a Server Audit Event'
		WHEN ([t].[name] LIKE '%Audit Lo%' OR [t].[name] LIKE'%Audit Server%') AND [xe].[xe_event_name] IS NOT NULL THEN 'The XE Event [' + [xe].[xe_event_name] + '] exists, but use Server Audit for this event instead'
		WHEN [xe].[xe_event_name] IS NULL THEN 'No comparable XE Event'
		ELSE [xe].[xe_event_name]
	END	[XE_Event],
	[xe].[package_name] [XE_Package]
FROM [sys].[trace_events] [t] 
LEFT OUTER JOIN [sys].[trace_xe_event_map] [xe] ON [t].[trace_event_id] = [xe].[trace_event_id]
ORDER BY [t].[name] ;
GO 

You can run this on SQL Server 2012 or higher.  You could run it on SQL Server 2008/2008R2, but remember that there was not a comparable event in Extended Events for every event in Trace until SQL Server 2012.  Thus you will have some unmapped events in the 2008/2008R2 releases.

Something that you’ll notice in the query: I’m accounting for Audit events.  The  Audit Feature was introduced in SQL Server 2008 but was Enterprise Edition only.  In SQL Server 2012, Server Audit is supported in all editions, so all Audit events from Trace should instead be captured with Server Audit (which leverages Extended Events behind the scenes).

Finally, don’t forget that if you’re looking to take an existing Trace and convert it to Extended Events, you can use Jonathan’s stored procedure to get the event session DDL.

Happy Extended Eventing!