Mapping SQL Trace to Extended Events

Since I started actively participating in SQL community, I found it is very common that people will approach to seek help on their SQL related issues. I do love to help people because it is always a win and win situation for both of us.

Recently, I had a phone conversation with one of the community members (CM) on one of the SQL issues. At some point, I asked him (CM) to capture some data for troubleshooting purpose. Below is what we discussed over the phone;

  • ME: Can use the extended event to capture the advised data?
  • CM: Can I use SQL Trace instead extended event (xEvents)?
  • ME: Why SQL Trace… why not extended event?
  • CM: I am very used to with SQL Trace. I find difficulties with the extended event because I don’t know what columns I need to select in the Extended Events. The column names are different in xEvents compare to SQL Trace.
  • ME: Agreed the fact that xEvent names don’t match up to SQL Trace event names

The challenge which he faced that motivates me to write this blog. In this blog, you will explore all the SQL Trace columns are mapped to which extended events columns. You can run the below code to view the SQL Trace event, and its associated columns are mapped to which extended events and their associated columns.

Below is the output of the script;

Trace Category NameTrace Event NamexEvent Package NamexEvent Name
BrokerBroker:Activationsqlserverbroker_activation
BrokerBroker:Connectionucsucs_connection_setup
BrokerBroker:Conversationsqlserverbroker_conversation
BrokerBroker:Conversation Groupsqlserverbroker_conversation_group
BrokerBroker:Corrupted Messagesqlserverbroker_corrupted_message
BrokerBroker:Forwarded Message Droppedsqlserverbroker_forwarded_message_dropped
BrokerBroker:Forwarded Message Sentsqlserverbroker_forwarded_message_sent
BrokerBroker:Message Classifysqlserverbroker_message_classify
BrokerBroker:Message Undeliverablesqlserverbroker_message_undeliverable
BrokerBroker:Mirrored Route State Changedsqlserverbroker_mirrored_route_state_changed
BrokerBroker:Queue Disabledsqlserverbroker_queue_disabled
BrokerBroker:Remote Message Acknowledgementsqlserverbroker_remote_message_acknowledgement
BrokerBroker:Transmissionsqlserverbroker_transmission_exception
CLRAssembly Loadsqlserverassembly_load
CursorsCursorClosesqlservercursor_close
CursorsCursorExecutesqlservercursor_execute
CursorsCursorImplicitConversionsqlservercursor_implicit_conversion
CursorsCursorOpensqlservercursor_open
CursorsCursorPreparesqlservercursor_prepare
CursorsCursorRecompilesqlservercursor_recompile
CursorsCursorUnpreparesqlservercursor_unprepare
DatabaseData File Auto Growsqlserverdatabase_file_size_change
DatabaseData File Auto Shrinksqlserverdatabase_file_size_change
DatabaseDatabase Mirroring Connectionucsucs_connection_setup
DatabaseDatabase Mirroring State Changesqlserverdatabase_mirroring_state_change
DatabaseLog File Auto Growsqlserverdatabase_file_size_change
DatabaseLog File Auto Shrinksqlserverdatabase_file_size_change
DeprecationDeprecation Announcementsqlserverdeprecation_announcement
DeprecationDeprecation Final Supportsqlserverdeprecation_final_support
Errors and WarningsAttentionsqlserverattention
Errors and WarningsBackground Job Errorsqlserverbackground_job_error
Errors and WarningsBitmap Warningsqlserverbitmap_disabled_warning
Errors and WarningsBlocked process reportsqlserverblocked_process_report
Errors and WarningsCPU threshold exceededsqlservercpu_threshold_exceeded
Errors and WarningsDatabase Suspect Data Pagesqlserverdatabase_suspect_data_page
Errors and WarningsErrorLogsqlservererrorlog_written
Errors and WarningsEventLogsqlservererror_reported
Errors and WarningsExceptionsqlosexception_ring_buffer_recorded
Errors and WarningsExchange Spill Eventsqlserverexchange_spill
Errors and WarningsExecution Warningssqlserverexecution_warning
Errors and WarningsHash Warningsqlserverhash_warning
Errors and WarningsMissing Column Statisticssqlservermissing_column_statistics
Errors and WarningsMissing Join Predicatesqlservermissing_join_predicate
Errors and WarningsSort Warningssqlserversort_warning
Errors and WarningsUser Error Messagesqlservererror_reported
Full textFT:Crawl Abortedsqlservererror_reported
Full textFT:Crawl Startedsqlserverfull_text_crawl_started
Full textFT:Crawl Stoppedsqlserverfull_text_crawl_stopped
LocksDeadlock graphsqlserverxml_deadlock_report
LocksLock:Acquiredsqlserverlock_acquired
LocksLock:Cancelsqlserverlock_cancel
LocksLock:Deadlocksqlserverlock_deadlock
LocksLock:Deadlock Chainsqlserverlock_deadlock_chain
LocksLock:Escalationsqlserverlock_escalation
LocksLock:Releasedsqlserverlock_released
LocksLock:Timeoutsqlserverlock_timeout
LocksLock:Timeout (timeout > 0)sqlserverlock_timeout_greater_than_0
ObjectsObject:Alteredsqlserverobject_altered
ObjectsObject:Createdsqlserverobject_created
ObjectsObject:Deletedsqlserverobject_deleted
OLEDBOLEDB Call Eventsqlserveroledb_call
OLEDBOLEDB DataRead Eventsqlserveroledb_data_read
OLEDBOLEDB Errorssqlserveroledb_error
OLEDBOLEDB Provider Informationsqlserveroledb_provider_information
OLEDBOLEDB QueryInterface Eventsqlserveroledb_query_interface
PerformanceAuto Statssqlserverauto_stats
PerformanceDegree of Parallelismsqlserverdegree_of_parallelism
PerformancePerformance statisticssqlserverquery_cache_removal_statistics
PerformancePerformance statisticssqlserverquery_pre_execution_showplan
PerformancePerformance statisticssqlserveruncached_sql_batch_statistics
PerformancePlan Guide Successfulsqlserverplan_guide_successful
PerformancePlan Guide Unsuccessfulsqlserverplan_guide_unsuccessful
PerformanceShowplan Allsqlserverquery_pre_execution_showplan
PerformanceShowplan All For Query Compilesqlserverquery_post_compilation_showplan
PerformanceShowplan Statistics Profilesqlserverquery_post_execution_showplan
PerformanceShowplan Textsqlserverquery_pre_execution_showplan
PerformanceShowplan Text (Unencoded)sqlserverquery_pre_execution_showplan
PerformanceShowplan XMLsqlserverquery_pre_execution_showplan
PerformanceShowplan XML For Query Compilesqlserverquery_post_compilation_showplan
PerformanceShowplan XML Statistics Profilesqlserverquery_post_execution_showplan
PerformanceSQL:FullTextQueryNULLNULL
Progress ReportProgress Report: Online Index Operationsqlserverprogress_report_online_index_operation
Query NotificationsQN: Dynamicssqlserverqn_dynamics
Query NotificationsQN: Parameter tablesqlserverqn_parameter_table
Query NotificationsQN: Subscriptionsqlserverqn_subscription
Query NotificationsQN: Templatesqlserverqn_template
ScansScan:Startedsqlserverscan_started
ScansScan:Stoppedsqlserverscan_stopped
Security AuditAudit Add DB User EventNULLNULL
Security AuditAudit Add Login to Server Role EventNULLNULL
Security AuditAudit Add Member to DB Role EventNULLNULL
Security AuditAudit Add Role EventNULLNULL
Security AuditAudit Addlogin EventNULLNULL
Security AuditAudit App Role Change Password EventNULLNULL
Security AuditAudit Backup/Restore EventNULLNULL
Security AuditAudit Broker Conversationsqlserverfulltextlog_written
Security AuditAudit Broker LoginNULLNULL
Security AuditAudit Change Audit EventNULLNULL
Security AuditAudit Change Database OwnerNULLNULL
Security AuditAudit Database Management EventNULLNULL
Security AuditAudit Database Mirroring LoginNULLNULL
Security AuditAudit Database Object Access EventNULLNULL
Security AuditAudit Database Object GDR EventNULLNULL
Security AuditAudit Database Object Management EventNULLNULL
Security AuditAudit Database Object Take Ownership EventNULLNULL
Security AuditAudit Database Operation EventNULLNULL
Security AuditAudit Database Principal Impersonation EventNULLNULL
Security AuditAudit Database Principal Management EventNULLNULL
Security AuditAudit Database Scope GDR EventNULLNULL
Security AuditAudit DBCC EventNULLNULL
Security AuditAudit FulltextNULLNULL
Security AuditAudit Loginsqlserverlogin
Security AuditAudit Login Change Password EventNULLNULL
Security AuditAudit Login Change Property EventNULLNULL
Security AuditAudit Login FailedNULLNULL
Security AuditAudit Login GDR EventNULLNULL
Security AuditAudit Logoutsqlserverlogout
Security AuditAudit Object Derived Permission EventNULLNULL
Security AuditAudit Schema Object Access EventNULLNULL
Security AuditAudit Schema Object GDR EventNULLNULL
Security AuditAudit Schema Object Management EventNULLNULL
Security AuditAudit Schema Object Take Ownership EventNULLNULL
Security AuditAudit Server Alter Trace EventNULLNULL
Security AuditAudit Server Object GDR EventNULLNULL
Security AuditAudit Server Object Management EventNULLNULL
Security AuditAudit Server Object Take Ownership EventNULLNULL
Security AuditAudit Server Operation EventNULLNULL
Security AuditAudit Server Principal Impersonation EventNULLNULL
Security AuditAudit Server Principal Management EventNULLNULL
Security AuditAudit Server Scope GDR EventNULLNULL
Security AuditAudit Server Starts And Stopssqlserverserver_start_stop
Security AuditAudit Statement Permission EventNULLNULL
ServerMount TapeNULLNULL
ServerServer Memory Changesqlserverserver_memory_change
ServerTrace File CloseNULLNULL
SessionsExistingConnectionsqlserverexisting_connection
SessionsPreConnect:Completedsqlserverpreconnect_completed
SessionsPreConnect:Startingsqlserverpreconnect_starting
Stored ProceduresRPC Output Parametersqlserverrpc_completed
Stored ProceduresRPC:Completedsqlserverrpc_completed
Stored ProceduresRPC:Startingsqlserverrpc_starting
Stored ProceduresSP:CacheHitsqlserversp_cache_hit
Stored ProceduresSP:CacheInsertsqlserversp_cache_insert
Stored ProceduresSP:CacheMisssqlserversp_cache_miss
Stored ProceduresSP:CacheRemovesqlserversp_cache_remove
Stored ProceduresSP:Completedsqlservermodule_end
Stored ProceduresSP:Recompilesqlserversql_statement_recompile
Stored ProceduresSP:Startingsqlservermodule_start
Stored ProceduresSP:StmtCompletedsqlserversp_statement_completed
Stored ProceduresSP:StmtStartingsqlserversp_statement_starting
TransactionsDTCTransactionsqlserverdtc_transaction
TransactionsSQLTransactionsqlserversql_transaction
TransactionsTM: Begin Tran completedsqlserverbegin_tran_completed
TransactionsTM: Begin Tran startingsqlserverbegin_tran_starting
TransactionsTM: Commit Tran completedsqlservercommit_tran_completed
TransactionsTM: Commit Tran startingsqlservercommit_tran_starting
TransactionsTM: Promote Tran completedsqlserverpromote_tran_completed
TransactionsTM: Promote Tran startingsqlserverpromote_tran_starting
TransactionsTM: Rollback Tran completedsqlserverrollback_tran_completed
TransactionsTM: Rollback Tran startingsqlserverrollback_tran_starting
TransactionsTM: Save Tran completedsqlserversave_tran_completed
TransactionsTM: Save Tran startingsqlserversave_tran_starting
TransactionsTransactionLogsqlservertransaction_log
TSQLExec Prepared SQLsqlserverexec_prepared_sql
TSQLPrepare SQLsqlserverprepare_sql
TSQLSQL:BatchCompletedsqlserversql_batch_completed
TSQLSQL:BatchStartingsqlserversql_batch_starting
TSQLSQL:StmtCompletedsqlserversql_statement_completed
TSQLSQL:StmtRecompilesqlserversql_statement_recompile
TSQLSQL:StmtStartingsqlserversql_statement_starting
TSQLUnprepare SQLsqlserverunprepare_sql
TSQLXQuery Static Typesqlserverxquery_static_type
User configurableUserConfigurable:0sqlserveruser_event
User configurableUserConfigurable:1sqlserveruser_event
User configurableUserConfigurable:2sqlserveruser_event
User configurableUserConfigurable:3sqlserveruser_event
User configurableUserConfigurable:4sqlserveruser_event
User configurableUserConfigurable:5sqlserveruser_event
User configurableUserConfigurable:6sqlserveruser_event
User configurableUserConfigurable:7sqlserveruser_event
User configurableUserConfigurable:8sqlserveruser_event
User configurableUserConfigurable:9sqlserveruser_event

Thanks!

Leave a comment

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