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”
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.
Hi Jonathan,
I think there is a minor typo. It’s ‘sys.trace_xe_event_map’, not ‘dbo.trace_xe_event_map’.
Love the series!