Several years back, when Extended Events was brand new, it was downright painful to try and convert the classic Profiler or Server Side trace to something meaningful and useful within Extended Events.
That was years ago and really is ancient history now. Microsoft has provided a couple of maps to help us translate the petroglyphs of Profiler into the high tech, smooth running, efficient engine and language of XEvents. This article is going to demonstrate a quick correlation between events in these two tools.
Maps
If you have browsed XEvents to any extent you should probably be familiar with at least one map object that Microsoft has given us. That particular object is sys.dm_xe_map_values and I wrote a bit about it here. That object presents some good information about various data values within XEvents that are delivered with the Event payload as well as some hidden gems that can be extra useful for exploring SQL Server internals. The point is, maps is not necessarily a new concept.
While the concept of a map is not new within XEvents, the implementation in this case is a tad different. You see, the dm_xe_map_values object is more like an EAV object while the map I will introduce today is more of an ordinary lookup table. Let’s take a look.
The map I want to concentrate on for now is the sys.trace_xe_event_map table. You read that correctly – it is a table. We don’t have very many “tables” when dealing with XEvents, but this one happens to be. As Microsoft Docs describes it, the table “contains one row for each Extended Events event that is mapped to a SQL Trace event class.”
And to show that it is indeed a table, you can run a check like the following.
select schema_name(ao.schema_id) as SchemaName, ao.name as ObjName , ao.type_desc, ao.modify_date, ao.is_ms_shipped from master.sys.all_objects ao where name = 'trace_xe_event_map';
So this table contains a mapping between Extended Event events and SQL Trace events. If we are curious to see that mapping, we can do a simple query and see the trace_event_id and the XE event name along with the XE package that is related to that specific event. That said, most of us do not have the trace_event_id memorized to human friendly terms so we need to do a little bit more. That is fine because it is possible for us to also see the relationship between those trace ids and the trace event names. Let’s get a little more creative with the query.
Before doing that though, I do want to lay out some baseline info. There are 180 trace events and of those there are 139 mappings in the map table between SQL trace and XE. This tells us immediately that there is not a one-to-one mapping. What this does not divulge just yet is that there may be a many-to-one mapping (ok, there is a single many-to-one mapping). We will look a bit at the differences in mappings.
SELECTxm.xe_event_name
, te.name AS trace_event_name
, tc.name AS TraceCategory
, sub.PackageName
, sub.DLLName
, sub.file_version
, sub.product_version
FROMsys.trace_xe_event_map xm
RIGHT OUTER JOIN sys.trace_events te
ON xm.trace_event_id = te.trace_event_id
RIGHT OUTER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
LEFT OUTER JOIN ( SELECT xo.name AS EventName
, xo.description AS EventDescription
, xp.name AS PackageName
, REVERSE(
LEFT(REVERSE(olm.name), CHARINDEX(
''
, REVERSE(
olm.name))
- 1)) AS DLLName
, olm.file_version
, olm.product_version
FROMsys.dm_xe_objects xo
INNER JOIN sys.dm_xe_packages xp
ON xo.package_guid = xp.guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
WHERE xo.object_type = 'event' ) sub
ON xm.xe_event_name = sub.EventName
AND xm.package_name = sub.PackageName
ORDER BY xm.xe_event_name, tc.name;And the results will look something like the following.
In the previous image, I highlighted three areas in different colors. First, note the red box surrounding the row count output in my results. Recall that I said there were only 180 events and only 139 rows in the map table. Clearly, we have a map that has a many-to-one relationship.
Next up is the blue box. The only thing we can see here is that there is a trace event with no mapping to an Extended Event. We expected this result given there were only 139 maps. Of interest here is that most of the unmapped events are Audit related. While SQL Audit relies heavily on the XE Engine, the events from Trace do not map directly to events in XEvents.
The last call-out is the green box. This is there to show the clear mapping between trace events and XEvents events. For the most part, this has not changed clear through SQL Server 2017. If you will notice, I have included the product version, dll file name and the dll version info. That version info helps to underscore what has changed with SQL Trace which is practically nothing.
This brings us to the question on everybody’s mind: What is the many-to-one map? That is an easy answer but the explanation is not quite as easy.
SELECTtrace_event_id FROMsys.trace_xe_event_map GROUP BY trace_event_id HAVING COUNT(trace_event_id) > 1;
Running that query, I will find that event_id 165 has more than one mapping in the map table. Let’s see what that translates to in human terms.
SELECT em.package_name, em.xe_event_name, te.name AS trace_event_name FROM sys.trace_xe_event_map em INNER JOIN sys.trace_events te ON em.trace_event_id = te.trace_event_id WHERE em.trace_event_id = 165;
And this is what it looks like:
So, “Performance Statistics” happens to be the event that maps to multiple different XEvent events. That said, you may wonder why “query_pre_execution_showplan” doesn’t map to something like “Showplan XML” or maybe “Showplan All” which produce the execution plan in similar fashion. I will leave that mystery to you as your deep dive homework assignment.
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.
