Converting SQL Trace to Extended Events in SQL Server 2012

With the growing popularity of Extended Events in SQL Server 2012 with the UI enhancements that I’ve blogged about on a number of posts (SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface, SQL Server 2012 Extended Events Update – 2 – The SSMS UI Part 2, SQL Server 2012 Extended Events Update – 3 – Viewing Target Data).  To make use of Extended Events and the performance benefits of collecting data from SQL Server using Extended Events instead of SQL Trace, I’ve written a comprehensive converter to migrate existing trace definitions from SQL Trace to Extended Events for SQL Server 2012 RTM.  In the past the Extended Events team at Microsoft has blogged about how to perform this conversion through the use of SQLCLR in their blog post Migrating SQL Trace to Extended Events, however, due to changes in the catalog views for mapping Trace Events to Extended Events, as well as a lack of a comprehensive mapping of SQL Trace columns to their respective Extended Events event columns and actions, this method falls short of being a solution to converting from SQL Trace to Extended Events.

I personally spent a number of hours manually mapping the SQL Trace Events column mappings to the related Extended Events event column mappings to build a cross reference table of events between the two environments and the result is a comprehensive set of scripts that can migrate SQL Trace definitions into Extended Events with full comments of what columns have been mapped as Extended Events columns or Actions, and the columns and events that no longer match up based on the Extended Events event definitions.

To this effort, below is a TSQL Script that will convert SQL Trace definitions to Extended Events within your own environment.  The output of this script for the default trace in SQL Server 2012 is as follows:

IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'XE_Default_Trace')
 DROP EVENT SESSION [XE_Default_Trace] ON SERVER;
GO
CREATE EVENT SESSION [XE_Default_Trace]
ON SERVER
/* Audit Login Failed is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Database Scope GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Addlogin Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login GDR Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Login Change Property Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Login to Server Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add DB User Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Member to DB Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Add Role Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Backup/Restore Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit DBCC Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Audit Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Change Database Owner is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Schema Object Take Ownership Event is not implemented in Extended Events it may be a Server Audit Event */
/* Audit Server Alter Trace Event is not implemented in Extended Events it may be a Server Audit Event */
ADD EVENT sqlserver.database_file_size_change(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
 )
),
/* Log File Auto Grow is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Data File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
/* Log File Auto Shrink is implemented as the sqlserver.database_file_size_change event in Extended Events */
ADD EVENT sqlserver.database_mirroring_state_change(
 ACTION
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
 )
),
ADD EVENT sqlserver.errorlog_written(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — Severity not implemented in XE for this event
   — State not implemented in XE for this event
   — Error not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_started(
 ACTION
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.full_text_crawl_stopped(
 ACTION
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   — ServerName not implemented in XE for this event
 )
),
ADD EVENT sqlserver.hash_warning(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_column_statistics(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.missing_join_predicate(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.object_altered(
 ACTION
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_created(
 ACTION
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.object_deleted(
 ACTION
 (
     package0.attach_activity_id — IntegerData from SQLTrace
   , sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — BigintData1 not implemented in XE for this event
 )
),
ADD EVENT sqlserver.plan_guide_unsuccessful(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
   — TextData not implemented in XE for this event
 )
),
ADD EVENT sqlserver.server_memory_change(
 ACTION
 (
     package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
),
ADD EVENT sqlserver.server_start_stop(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
 )
),
ADD EVENT sqlserver.sort_warning(
 ACTION
 (
     sqlserver.client_app_name — ApplicationName from SQLTrace
   , sqlserver.client_hostname — HostName from SQLTrace
   , sqlserver.client_pid — ClientProcessID from SQLTrace
   , sqlserver.database_id — DatabaseID from SQLTrace
   , sqlserver.database_name — DatabaseName from SQLTrace
   , package0.event_sequence — EventSequence from SQLTrace
   , sqlserver.is_system — IsSystem from SQLTrace
   , sqlserver.nt_username — NTUserName from SQLTrace
   , sqlserver.nt_username — NTDomainName from SQLTrace
   , sqlserver.request_id — RequestID from SQLTrace
   , sqlserver.server_instance_name — ServerName from SQLTrace
   , sqlserver.server_principal_name — LoginName from SQLTrace
   , sqlserver.server_principal_sid — LoginSid from SQLTrace
   , sqlserver.session_id — SPID from SQLTrace
   , sqlserver.session_resource_group_id — GroupID from SQLTrace
   , sqlserver.session_server_principal_name — SessionLoginName from SQLTrace
   , sqlserver.transaction_id — TransactionID from SQLTrace
   , sqlserver.transaction_sequence — XactSequence from SQLTrace
 )
)
ADD TARGET package0.event_file
(
 SET filename = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XE_Default_Trace.xel',
  max_file_size = 20,
  max_rollover_files = 5
)

sp_SQLskills_ConvertTraceToExtendedEvents.sql (55.41 kb)

25 thoughts on “Converting SQL Trace to Extended Events in SQL Server 2012

  1. Hello,

    Getting this error on a MSSQL 2012 Standard SP1

    Msg 468, Level 16, State 9, Procedure sp_SQLskills_ConvertTraceToExtendedEvents, Line 84
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “French_CI_AS” in the equal to operation.

    Line 84:
    JOIN sys.dm_xe_object_columns AS oc
    ON o.name = oc.object_name
    AND o.package_guid= oc.object_package_guid

    Any way to get around this?

    Thanks

    1. Hey Craig,

      Whats the output of:

      select name, collation_name from sys.databases;

      From your SQL Server? Then what database did you call the Stored Procedure from? You have mismatching database collations, but the failure point you highlight is on a JOIN between two system views from Extended Events, so I’m not sure why it is failing unless the error is just misleading, which happens in SQL Server at times.

  2. My bad, I got the wrong line from SSMS

    ON tab.event_package_name = txem.package_name AND tab.event_name = txem.xe_event_name

    Here is the output.

    name collation_name
    master French_CI_AS
    tempdb French_CI_AS
    model French_CI_AS
    msdb French_CI_AS
    dba SQL_Latin1_General_CP1_CI_AS

    I used master as was defined by your stored procedure. I tried putting it in the dba database just to see if changing collations would work and I still got the same error.

    1. Hey Craig,

      I don’t have a French_CI_AS instance to test on, but you can try adding COLLATE SQL_Latin1_General_CP1_CI_AS to the line above that is erroring out to force the collation for the string comparison and it should work.

        1. Hey Craig,

          Awesome, thanks for returning to let me know that. I’ll go back in and update the code so that it forces the collation to prevent problems for others in a bit. I really appreciate the feedback and willingness to test the recommended change to see if it fixed the issue.

          Thanks again!

          1. Johnathan, I have taken your function and used it to create a session for the trace replay template in Profiler. That has worked just fine. Now I am working on creating a query to replay whats being stored in the ring_buffer by that XE session. The only problem is that the columns change with every event type. Logout, rpc_completed, sql_batch_completed, etc. Have you worked on a query to replay events from the session yet?


            SELECT
            theNodes.event_data.value('(@name)[1]', 'nvarchar(max)') AS event_name,
            theNodes.event_data.value('(data/value)[1]', 'nvarchar(max)') AS CPU_TIME,
            theNodes.event_data.value('(data/value)[2]', 'nvarchar(max)') AS DURATION,
            theNodes.event_data.value('(data/value)[3]', 'nvarchar(max)') AS PHYSICAL_READS,
            theNodes.event_data.value('(data/value)[4]', 'nvarchar(max)') AS LOGICAL_READS,
            theNodes.event_data.value('(data/value)[5]', 'nvarchar(max)') AS WRITES,
            theNodes.event_data.value('(data/value)[6]', 'nvarchar(max)') AS ROW_COUNT,
            theNodes.event_data.value('(data/value)[7]', 'nvarchar(max)') AS RESULT,
            theNodes.event_data.value('(data/value)[8]', 'nvarchar(max)') AS BATCH_TEXT,
            theNodes.event_data.value('(action/value)[1]', 'nvarchar(max)') AS SPID,
            theNodes.event_data.value('(action/value)[2]', 'nvarchar(max)') AS SERVER_PRINCIPAL_NAME,
            theNodes.event_data.value('(action/value)[3]', 'nvarchar(max)') AS SERVER_INSTANCE_NAME,
            theNodes.event_data.value('(action/value)[4]', 'nvarchar(max)') AS REQUEST_ID,
            theNodes.event_data.value('(action/value)[5]', 'nvarchar(max)') AS NT_USERNAME,
            theNodes.event_data.value('(action/value)[6]', 'nvarchar(max)') AS IS_SYSTEM,
            theNodes.event_data.value('(action/value)[7]', 'nvarchar(max)') AS SEQUENCE,
            theNodes.event_data.value('(action/value)[8]', 'nvarchar(max)') AS DATABASE_NAME,
            theNodes.event_data.value('(action/value)[9]', 'nvarchar(max)') AS DATABASE_ID,
            theNodes.event_data.value('(action/value)[10]', 'nvarchar(max)') AS CLIENT_PID,
            theNodes.event_data.value('(action/value)[11]', 'nvarchar(max)') AS CLIENT_HOSTNAME,
            theNodes.event_data.value('(action/value)[12]', 'nvarchar(max)') AS CLIENT_APP_NAME
            FROM
            (
            SELECT
            CONVERT(XML, st.target_data) AS ring_buffer
            FROM sys.dm_xe_sessions s
            JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
            WHERE s.name = 'DBA_Replay'
            ) AS theData
            CROSS APPLY
            theData.ring_buffer.nodes('//RingBufferTarget/event') theNodes (event_data)
            WHERE theNodes.event_data.value('(@name)[1]', 'nvarchar(max)') = 'sql_batch_completed'

            1. Hey Craig,

              There are a lot of problems with trying to do a replay from Extended Events at this point, so are simple like what you are finding above, others are a lot more complex, for example the way different events were combined together and you’d have to split the event into two separate events in some cases to get the replay capture. The replay tools aren’t designed to work with Extended Events data at this point so it’s not a viable means of collecting replay data. At some point in the future the replay tools will be modified so that you can use a .xel file as the replay source. Until then SQL Trace is your best method of doing replay capture. Keep in mind tools like Distributed Replay aren’t driven at 2012 users, they are driven at 2005/2008 users to help with upgrade testing.

  3. This is an excellent post Jonathan and it’s a class of it’s own.

    Well, I tried this on my 2012 Development edition , however I noticed that profiler ‘TextData’ column is NOT converted to an xEvent action ( Well this is the only one I have noticed) ? Could this be because I need to do apply some sql 2012 service packs ? Your reply would be very much appreciated and thanks in advance

    Thanks
    raj

    1. Hey Raj,

      The TextData column in trace is overloaded and has different meanings for different Events. For example the SQL:StmtCompleted event it is the statement text which maps to the Statement column for the sql_statement_completed Extended Event. If there is a related column for what TextData was in SQL Trace, it’s been mapped out in the conversion process to the appropriate Extended Events column for the event, or to an Action if required.

  4. Hi Jonathan,
    Thanks a lot for your reply and I completly understand your point.

    This is what I tried , but I am not seeing the xEvent action fopr the columns Textdata and Duration that we expect ? ( There should be something wrong with my SQL version ๐Ÿ™‚ )

    Profiler Trace
    ==============
    — 12 = > SQL:BatchCompleted
    exec sp_trace_setevent @TraceID, 12, 1, @on –TextData
    exec sp_trace_setevent @TraceID, 12, 12, @on — SPID
    exec sp_trace_setevent @TraceID, 12, 13, @on — Duration

    — Xevents Action Generated
    ADD EVENT sqlserver.sql_batch_completed(ACTION
    (
    sqlserver.session_id — SPID from SQLTrace
    )
    –===============================
    I will try this on an enterprise edition using your script and will see what is getting genrenated. I will also apply the service pack 1 on my Developer Edition and try again .

    Thanks
    Raj

    1. Hey Rajesh,

      The TextData is the equivalent of the batch_text column for the sql_batch_completed event, and the duration is a column of the event itself. Your expectations that Extended Events works like SQL Trace are the cause of the confusion here. The two are not built the same way.

  5. Hi Jonathan,

    Thought I will let you know what I have found out

    select * from sys.trace_xe_action_map — is missing few records

    I have just run the above stmt and noticed that there are quite a few ‘trace_column_id’ valus missing from it . 1 and 13 IDs are two of them and I believe this is the problem.

    Thanks
    Raj

    1. Hey Rajesh,

      There is nothing missing in the mapping or in your version of SQL Server. You don’t understand the core concepts behind Extended Events or how it is implemented, which is entirely different than SQL Trace. I would suggest that you start at Day 1 of my XEvent a Day series and read through the first seven posts which cover the differences and how to get started with Extended Events.

  6. Hi Jonathan,
    You are right , I am looking for a like for like comparison and I need to understand the concept of xEvent first:) I will definitly go through your start 1 days of your XEvent a Day .

    Thanks for spening some time to asnwer my silly questions .

    Thanks
    Raj

  7. Hi Jonathan,

    After reading few of your articles on Extended events it’s all making perfect sense to me and thanks a lot . I am very exited now and will go through all of your 31 days of Extended Events.

    Once again , great stuff and thanks a lot .

    Thanks
    Raj

  8. Hello Jonathan,
    I just tried to create the default trace as XE session and my SQL 2012 (11.00.2845) complained about package0.attach_activity_id being the action list of 3 of the events. As far as I could research, there was a connect item (http://connect.microsoft.com/SQLServer/feedback/details/334899/extended-events-error-when-attempting-to-use-attach-activity-id-action) based on which I removed the actions and the Event session was created successfully. What is attach_activity_id used for and can I live without it? ๐Ÿ™‚

  9. Hi Jonathan,
    First off, it was great to meet and talk to you at the PASS summit in Charlotte! You might remember me as the guy who bugged you about how you do “alerting solutions” for clients after your session on the system_health XE session. ๐Ÿ˜‰

    Secondly, thanks for the addition of another gem to my archive of utility scripts with this post, and the work you’ve done to manually map all these trace events to XE. This just seemed like one of those things that really needed to be done and would benefit a lot of people, and I’m glad you stepped up.

    Last thing. I just wanted to let you know I added a little alteration and to get your take on it. It seemed a shame that this would only work on 2012+ instances simply because of the addition of the sys.trace_xe_event_map and sys.trace_xe_action_map in 2012, especially because (I think) most of the TSQL the stored proc would generate would still be valid on a 2008 instance. On one of my 2012 instances, the event map table had only 138 rows and the action map table had only 22, so I thought it would be a decent idea to just manually populate table variables with those values if I’m on a 2008 instance, and then use those table variables in the joins downstream. I created the sp_SQLskills_ConvertTraceToExtendedEvents proc on a 2008 instance with this mod and it seems to generate a script for the default trace just fine.

    Let me know what you think of the idea and if you’d like to see the code.

    Thanks again!

    1. Hey Bob,

      The problem is that most of the events don’t exist in 2008 and for those that do exist, the columns are different in 2012, and the events won’t have parity with Trace in 2008. If the DDL works it would be luck only.

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.