Convert an Existing SQL Trace Script to an Extended Events Session

Raksts
03/03/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

If you have an existing SQL Trace script that you want to convert to an Extended Events session, you can use the procedures in this topic to create an equivalent Extended Events session. By using the information in the trace_xe_action_map and trace_xe_event_map system tables, you can collect the information that you must have to do the conversion.

The steps include the following:

Execute the existing script to create a SQL Trace session, and then obtain the ID of the trace.
Run a query that uses the fn_trace_geteventinfo function to find the equivalent Extended Events events and actions for each SQL Trace event class and its associated columns.
Use the fn_trace_getfilterinfo function to list the filters and the equivalent Extended Events actions to use.
Manually create an Extended Events session, using the equivalent Extended Events events, actions, and predicates (filters).

To obtain the trace ID

Open the SQL Trace script in Query Editor, and then execute the script to create the trace session. Note that the trace session does not need to be running to complete this procedure.
Obtain the ID of the trace. To do this, use the following query:
```
SELECT * FROM sys.traces;  
GO  
```
Note

Trace ID 1 typically indicates the default trace.

To determine the Extended Events equivalents

To determine the equivalent Extended Events events and actions, run the following query, where trace_id is set to the value of the trace ID that you obtained in the previous procedure.

Note

In this example, the trace ID for the default trace (1) is used.
```
USE MASTER;  
GO  
DECLARE @trace_id int;  
SET @trace_id = 1;  
SELECT DISTINCT el.eventid, em.package_name, em.xe_event_name AS 'event'  
   , el.columnid, ec.xe_action_name AS 'action'  
FROM (sys.fn_trace_geteventinfo(@trace_id) AS el  
   LEFT OUTER JOIN sys.trace_xe_event_map AS em  
      ON el.eventid = em.trace_event_id)  
LEFT OUTER JOIN sys.trace_xe_action_map AS ec  
   ON el.columnid = ec.trace_column_id  
WHERE em.xe_event_name IS NOT NULL AND ec.xe_action_name IS NOT NULL;  
```
The equivalent Extended Events event ID, package name, event name, column ID and action name are returned. You will use this output in the procedure "To create the Extended Events session" later in this topic.

In some cases, the filtered column maps to an event data field that is included by default in the Extended Events event. Therefore, the "Extended_Events_action_name" column will be NULL. If this occurs, you must do the following to determine which data field is equivalent to the filtered column:
1. For the actions that return NULL, identify which SQL Trace event classes in the script contain the column that is being filtered.
  
  For example, you may have used the SP:StmtCompleted event class, and specified a filter on the Duration trace column name (SQL Trace event class ID 45, and SQL Trace column ID 13). In this case, the action name will appear as NULL in the query results.
2. For each SQL Trace event class that you identified in the previous step, find the equivalent Extended Events event name. (If you are not sure of the equivalent event name, use the query in the topic View the Extended Events Equivalents to SQL Trace Event Classes.)
3. Use the following query to identify the correct data fields to use for the events that you identified in the previous step. The query shows the Extended Events data fields in the "event_field" column. In the query, replace <event_name> with the name of an event that you specified in the previous step.
```
SELECT xp.name package_name, xe.name event_name  
   ,xc.name event_field, xc.description  
FROM sys.trace_xe_event_map AS em  
INNER JOIN sys.dm_xe_objects AS xe  
   ON em.xe_event_name = xe.name  
INNER JOIN sys.dm_xe_packages AS xp  
   ON xe.package_guid = xp.guid AND em.package_name = xp.name  
INNER JOIN sys.dm_xe_object_columns AS xc  
   ON xe.name = xc.object_name  
WHERE xe.object_type = 'event' AND xc.column_type <> 'readonly'  
   AND em.xe_event_name = '<event_name>';  
```
  For example, the SP:StmtCompleted event class maps to the sp_statement_completed Extended Events event. If you specify sp_statement_completed as the event name in the query, the "event_field" column shows the fields that are included by default with the event. Looking at the fields, you can see that there is a "duration" field. To create the filter in the equivalent Extended Events session, you would add a predicate such as "WHERE duration > 0". For an example, see the "To create the Extended Events session" procedure in this topic.

To create the Extended Events session

Use Query Editor to create the Extended Events session, and to write the output to a file target. The following steps describe a single query, with explanations to show you how to build the query. For the full query example, see the "Example" section of this topic.

Add statements to create the event session, replacing session_name with the name that you want to use for the Extended Events session.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='session_name')  
   DROP EVENT SESSION [Session_Name] ON SERVER;  
CREATE EVENT SESSION [Session_Name]  
ON SERVER;

Add the Extended Events events and actions that were returned as output in the procedure "Determine the Extended Events equivalents", and add the predicates (filters) that you identified in the procedure "To determine the filters that were used in the script".

The following example uses a SQL Trace script that includes the SQL:StmtStarting and SP:StmtCompleted event classes, with filters for session ID and duration. Sample output for the query in the "Determine the Extended Events equivalents" procedure returned the following result set:

Eventid  package_name  event                   columnid  action  
44       sqlserver     sp_statement_starting   6         nt_username  
44       sqlserver     sp_statement_starting   9         client_pid  
44       sqlserver     sp_statement_starting   10        client_app_name  
44       sqlserver     sp_statement_starting   11        server_principal_name  
44       sqlserver     sp_statement_starting   12        session_id  
45       sqlserver     sp_statement_completed  6         nt_username  
45       sqlserver     sp_statement_completed  9         client_pid  
45       sqlserver     sp_statement_completed  10        client_app_name  
45       sqlserver     sp_statement_completed  11        server_principal_name  
45       sqlserver     sp_statement_completed  12        session_id

To convert this to the Extended Events equivalent, the sqlserver.sp_statement_starting and the sqlserver.sp_statement_completed events are added, with a list of actions. Predicate statements are included as WHERE clauses.

ADD EVENT sqlserver.sp_statement_starting  
   (ACTION  
      (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
      )  
   WHERE sqlserver.session_id = 59   
   ),  

ADD EVENT sqlserver.sp_statement_completed  
   (ACTION  
      (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
      )  
   WHERE sqlserver.session_id = 59 AND duration > 0  
   )

Add the asynchronous file target, replacing the file paths with the location where you want to save the output. When specifying the file target, you must include a log file and metadata file path file.
```
ADD TARGET package0.asynchronous_file_target(  
   SET filename='c:\temp\ExtendedEventsStoredProcs.xel', metadatafile='c:\temp\ExtendedEventsStoredProcs.xem');  
```

To view the results

You can use the sys.fn_xe_file_target_read_file function to view the output. To do this, run the following query, replacing the file paths with the paths that you specified:

SELECT *, CAST(event_data as XML) AS 'event_data_XML'  
FROM sys.fn_xe_file_target_read_file('c:\temp\ExtendedEventsStoredProcs*.xel', 'c:\temp\ExtendedEventsStoredProcs*.xem', NULL, NULL);

Note

Casting the event data as XML is optional.

For more information about the sys.fn_xe_file_target_read_file function, see sys.fn_xe_file_target_read_file (Transact-SQL).

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='session_name')  
   DROP EVENT SESSION [session_name] ON SERVER;  
CREATE EVENT SESSION [session_name]  
ON SERVER  

ADD EVENT sqlserver.sp_statement_starting  
   (ACTION  
   (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
   )  
   WHERE sqlserver.session_id = 59   
   ),  

ADD EVENT sqlserver.sp_statement_completed  
   (ACTION  
   (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
   )  
   WHERE sqlserver.session_id = 59 AND duration > 0  
   );  

ADD TARGET package0.asynchronous_file_target  
   (SET filename='c:\temp\ExtendedEventsStoredProcs.xel', metadatafile='c:\temp\ExtendedEventsStoredProcs.xem');

Example

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='session_name')  
   DROP EVENT SESSION [session_name] ON SERVER;  
CREATE EVENT SESSION [session_name]  
ON SERVER  
  
ADD EVENT sqlserver.sp_statement_starting  
   (ACTION  
   (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
   )  
   WHERE sqlserver.session_id = 59   
   ),  
  
ADD EVENT sqlserver.sp_statement_completed  
   (ACTION  
   (  
      sqlserver.nt_username,  
      sqlserver.client_pid,  
      sqlserver.client_app_name,  
      sqlserver.server_principal_name,  
      sqlserver.session_id  
   )  
   WHERE sqlserver.session_id = 59 AND duration > 0  
   )  
  
ADD TARGET package0.asynchronous_file_target  
   (SET filename='c:\temp\ExtendedEventsStoredProcs.xel', metadatafile='c:\temp\ExtendedEventsStoredProcs.xem');

Kopīgot, izmantojot

Convert an Existing SQL Trace Script to an Extended Events Session

To obtain the trace ID

To determine the Extended Events equivalents

To create the Extended Events session

To view the results

Example

See Also

Atsauksmes

Papildu resursi