Correlate SQL Trace and Actions

, 2018-01-01

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 '%Audit%'
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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads