I have recently written about the pains of correlating SQL Trace events to Extended Events (XE) events. That article can be found here. At that time, I did not dive into another facet of trace that truly needs converting as well. That component is known as “Actions” in the world of XE.
In the world of profiler and SQL Trace, these actions were an actual part of the payload for the Trace event. In XE, these actions are more like add-ons. I go into further depth about what an action in XE is – here. I recommend reading that article. Due to that, I won’t be diving into great detail here about what an Action is.
Actions
That said, there is one thing in regards to actions in XE that I think would be helpful in translating Traces to XE. In XE, we sometimes see an action referenced as a global field. This is in stark contrast to what the Trace equivalent would be – a local field. Rough translation is that the actions in Trace were localized to every event that needed that particular data. In XE, the field has become more like a function and can be applied across many different events without being tied to every event specifically. Thus – it is renamed to “global field” in many different places.
Knowing that we migrate from localized fields to global fields is helpful in trying to map the old world of SQL Trace to the new and improved world of XE. In addition to that, we have some objects to introduce that will help us translate. Those objects are:
- sys.trace_event_bindings – a catalog view introduced in 2005 that contains a list of all possible usage combinations of events and columns.
- sys.trace_columns – a catalog view introduced in 2008 which contains a list of all trace event columns.
- sys.trace_xe_action_map – a table that contains a row for each XE action that is mapped to a SQL Trace Column.
I will use the data from these objects to map to the XE actions – with a few caveats. In order to map everything properly, each of these objects is required because they have just a tiny bit of the needed information in order to eventually get to the big picture. When I bring it all together, I will have a script something like this.
USE master;
GO
SELECT teb.trace_event_id
, te.name AS 'Event Class'
, xem.package_name AS 'Package'
, xem.xe_event_name AS 'XEvent Name'
, teb.trace_column_id
, tc.name AS 'SQL Trace Column'
, xam.xe_action_name AS 'Extended Events action'
FROM sys.trace_events te
LEFT OUTER JOIN sys.trace_xe_event_map xem
ON te.trace_event_id = xem.trace_event_id
LEFT OUTER JOIN sys.trace_event_bindings teb
ON xem.trace_event_id = teb.trace_event_id
LEFT OUTER JOIN sys.trace_columns tc
ON teb.trace_column_id = tc.trace_column_id
LEFT OUTER JOIN sys.trace_xe_action_map xam
ON tc.trace_column_id = xam.trace_column_id
WHERE xam.xe_action_name IS NOT NULL
AND te.name NOT LIKE '{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}Audit{529e71a51265b45c1f7f96357a70e3116ccf61cf0135f67b2aa293699de35170}'
ORDER BY teb.trace_event_id, teb.trace_column_id;You may have noticed here that I am excluding some results. I am not interested in anything that does not map to an action in the world of XE. So I am chucking those empty action results to the side. In addition, I am tossing anything that is Audit related. If it is Audit related, I really need to be using the SQL Audit feature (though it is built fundamentally on XE) instead of XE. So anything that is SQL Audit related just needs to be discarded from these results.
This will yield a whopping 2145 actions that are mapped between the local SQL Trace fields and the global XE fields called actions.
If I contrast that to a count of available actions in XE, you will likely see that there is a significant difference and that a lot of inefficiency has been removed.
SELECT * FROM sys.dm_xe_objects xo WHERE xo.object_type = 'action';
Depending on version of SQL Server you will get a slightly different result. Here is a quick cheat sheet I worked up for different versions.
As you can see from the image, none of the versions of SQL Server with XE exceed 55 actions (global fields). Compare that to over 2000 results in the old trace world with only 180 events, and I think you would agree it was highly inefficient.
The Wrap
If we browse the data made available to us, we can see that there is adequate info to help us convert all of those SQL Traces to the high-performing XE versions that we should be using these days. I encourage you to browse the capabilities of XE as you work towards converting your profiler based traces to the much friendlier and more powerful counterparts.
If you are interested in learning more about Extended Events, I recommend you read my “60 day” series of articles on Extended Events. The series continues to grow and covers a pretty decent depth and breadth on the topic.
