An XEvent a Day (20 of 31) – Mapping Extended Events to SQL Trace

One of the biggest problems that I had with getting into Extended Events was mapping the Events available in Extended Events to the Events that I knew from SQL Trace.  With so many Events to choose from in Extended Events, and a different organization of the Events, it is really easy to get lost when trying to find things.  Add to this the fact that Event names don’t match up to Trace Event names in SQL Server 2008 and 2008 R2, and not all of the Events from Trace are implemented in SQL Server 2008 and 2008 R2, and it gets really confusing really fast.  For my presentation this year at SQLBits 7, I sat down with Excel and mapped out the Events that exist in Extended Events to their corresponding SQL Trace Event, and two of the slides in my deck were tables of these mappings. 

TraceCategory TraceEvent PackageName XEEventName
Deprecation Deprecation Announcement sqlserver deprecation_announcement
Deprecation Deprecation Final Support sqlserver deprecation_final_support
Errors and Warnings ErrorLog sqlserver errorlog_written
Errors and Warnings EventLog sqlserver error_reported
Errors and Warnings Exception sqlos exception_ring_buffer_recorded
Errors and Warnings User Error Message sqlserver error_reported
Full text FT:Crawl Aborted sqlserver error_reported
Locks Deadlock graph sqlserver xml_deadlock_report
Locks Lock:Acquired sqlserver lock_acquired
Locks Lock:Deadlock sqlserver lock_deadlock
Locks Lock:Released sqlserver lock_released
Locks Lock:Timeout sqlserver locks_lock_timeouts
Locks Lock:Timeout (timeout > 0) sqlserver locks_lock_timeout_greater_than_0
Stored Procedures RPC Output Parameter sqlserver rpc_completed
Stored Procedures RPC:Completed sqlserver rpc_completed
Stored Procedures RPC:Starting sqlserver rpc_starting
Stored Procedures SP:Completed sqlserver module_end
Stored Procedures SP:Recompile sqlserver sp_statement_starting
Stored Procedures SP:Starting sqlserver module_start
Stored Procedures SP:StmtCompleted sqlserver sp_statement_completed
Stored Procedures SP:StmtStarting sqlserver sp_statement_starting
TSQL SQL:StmtCompleted sqlserver sql_statement_completed
TSQL SQL:StmtRecompile sqlserver sql_statement_starting
TSQL SQL:StmtStarting sqlserver sql_statement_starting
User configurable UserConfigurable sqlserver user_settable

I have a script that creates a view for these in my administrative database, that is a part of my SQL Server 2008 configuration script for my environment. That script is attached to this blog post for use in your own environment.

In SQL Server Denali CTP1, a table has been added to the master database named dbo.trace_xe_event_map that provides a static mapping of each Trace Event to its corresponding Extended Events Event (This table should be in the sys schema to maintain uniformity in the product and I filed a Connect Feedback to move this that needs votes to have this changed).  Mike Wachal blogged about this table and how it and its partner table dbo.trace_xe_action_map can be used to migrate from SQL Trace to Extended Events in his blog post Migrating from SQL Trace to Extended Events.  He also includes a really cool SQLCLR Stored Procedure that will perform the conversion for you automagically. 

3 thoughts on “An XEvent a Day (20 of 31) – Mapping Extended Events to SQL Trace

  1. Hi Jonathan,

    Great series on XE – incredibly helpful! Do you happen to have the script mentioned above that relates XE events to SQL Trace events?

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.