You may recall an article I had written a couple months back describing how to use Extended Events to perform your SQL Server Profiler duties. If not, you can read all about it here.
In that article, I showed one common use-case for SQL Server Profiler and how to achieve the same result via Extended Events. What I didn’t show in that article was how to correlate all of your favorite trace/profiler options into the shiny new XE equivalents. Is there even a way to do that?
As luck would have it, there is a means to correlate trace/profiler events to Extended Event events (yeah that sounds weird). With the release of SQL Server 2012, Microsoft introduced a couple of catalog views to help with this correlation effort. Those views have a pretty straightforward naming convention. For example, one of the views is named sys.trace_xe_event_map while the other is sys.trace_xe_action_map. For this article, I will be focusing on the former.
Making the Upgrade
When looking to finally make the upgrade away from trace/profiler, a big key is to figure out if your favorite trace events are even an option at the next level. I have talked about finding events in XE in previous articles by trying to search for a specific topic that might apply to your current condition.
But if you are already familiar with specific trace events, you may just want/need to know what the new name is in XE. This is where those catalog views come into play. And in support of that, here is a query that can help in that upgrade effort:
SELECT te.trace_event_id,tc.name AS CategoryName
, CASE tc.type
WHEN 0 THEN 'Normal'
WHEN 1 THEN 'Connection'
WHEN 2 THEN 'ERROR'
END AS TraceEventType
,te.name AS TraceEventName
, xem.package_name, xem.xe_event_name
FROM sys.trace_xe_event_map xem
RIGHT OUTER JOIN sys.trace_events te
ON te.trace_event_id = xem.trace_event_id
INNER JOIN sys.trace_categories tc
ON te.category_id = tc.category_id
ORDER BY tc.category_id,te.trace_event_id ASC;Looking through the results, one would notice that not every trace event maps to an Extended Event event. The events that don’t map, in general, deal with audits (which is actually driven by Extended Events).
This is a really good start to getting you on your way to that much needed upgrade away from profiler/trace. What if there are several traces that are already in use or scripted for various reasons in the environment?
Decision Time
If you happen to have traces that are already deployed in the environment or in script form, it can be a bit of a tedious pain to convert those to Extended Events. Do you manually recreate the traces as XE sessions? Do you abandon the upgrade due to the effort and annoyance it will create? Or do you find some automated means of performing the conversion?
Of those three options, only two are valid options. Those options involve performing the conversion of the traces to XE sessions. There are pros and cons for each. You may opt to take your time and learn more about Extended Events by performing the manual upgrade, or you may choose to save time by using an automated routine.
Should you decide to try the automated routine, there is one already out and available to help you on your way. Jonathan Kehayias wrote the script and you can download it here.
