{"id":771,"date":"2016-06-22T08:15:31","date_gmt":"2016-06-22T15:15:31","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=771"},"modified":"2016-06-24T01:18:35","modified_gmt":"2016-06-24T08:18:35","slug":"mapping-events-from-trace-to-extended-events","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/","title":{"rendered":"Mapping Events from Trace to Extended Events"},"content":{"rendered":"<p>Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation.\u00a0 It&#8217;s not always intentional, and I don&#8217;t know if it&#8217;s because I just can&#8217;t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added a query that helps you see what event in Extended Events is comparable to the event you&#8217;re used to using in Trace\/Profiler.\u00a0 While most of the events in XE have a name similar to the event in Trace (e.g. sp_statement_completed for SP:StmtCompleted), some mappings aren&#8217;t so intuitive.\u00a0 For example, SP:Starting in Trace is module_start in XE, and SP:Completed in Trace is module_end in XE.\u00a0 That&#8217;s not so bad, but if you&#8217;re ever monitoring file growths, note that the database_file_size_change event in XE is the event for the following <strong><em>four<\/em> <\/strong>events in trace:\u00a0Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow,\u00a0and Log File Auto Shrink.<\/p>\n<p>(Note: if you happen to notice databases_data_file_size_changed or databases_log_file_size_changed in the list of XE, <em>do not<\/em> use either event.\u00a0 Both are deprecated and won&#8217;t return any information.\u00a0 Use database_file_size_change.)<\/p>\n<p>Anyway, those fun one-offs aside, you&#8217;ll see that the query and its output are pretty straightforward:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT \r\n\t&#x5B;t].&#x5B;trace_event_id] &#x5B;TraceEventID], \r\n\t&#x5B;t].&#x5B;name] &#x5B;Trace_Event], \r\n\tCASE\t\r\n\t\tWHEN &#x5B;t].&#x5B;name] LIKE '%Audit%' AND &#x5B;xe].&#x5B;xe_event_name] IS NULL THEN 'This event is not implemented in Extended Events, it may be a Server Audit Event'\r\n\t\tWHEN (&#x5B;t].&#x5B;name] LIKE '%Audit Lo%' OR &#x5B;t].&#x5B;name] LIKE'%Audit Server%') AND &#x5B;xe].&#x5B;xe_event_name] IS NOT NULL THEN 'The XE Event &#x5B;' + &#x5B;xe].&#x5B;xe_event_name] + '] exists, but use Server Audit for this event instead'\r\n\t\tWHEN &#x5B;xe].&#x5B;xe_event_name] IS NULL THEN 'No comparable XE Event'\r\n\t\tELSE &#x5B;xe].&#x5B;xe_event_name]\r\n\tEND\t&#x5B;XE_Event],\r\n\t&#x5B;xe].&#x5B;package_name] &#x5B;XE_Package]\r\nFROM &#x5B;sys].&#x5B;trace_events] &#x5B;t] \r\nLEFT OUTER JOIN &#x5B;sys].&#x5B;trace_xe_event_map] &#x5B;xe] ON &#x5B;t].&#x5B;trace_event_id] = &#x5B;xe].&#x5B;trace_event_id]\r\nORDER BY &#x5B;t].&#x5B;name] ;\r\nGO \r\n<\/pre>\n<p>You can run this on SQL Server 2012 or higher.\u00a0 You could run it on SQL Server 2008\/2008R2, but remember that there was not a comparable event in Extended Events for\u00a0<em>every<\/em> event in Trace until SQL Server 2012.\u00a0 Thus you will have some unmapped events in the 2008\/2008R2 releases.<\/p>\n<p>Something that you&#8217;ll notice in the query: I&#8217;m accounting for Audit events.\u00a0 The\u00a0 Audit Feature was introduced in SQL Server 2008 but was Enterprise Edition only.\u00a0 In SQL Server 2012, Server Audit is supported in all editions, so all Audit events from Trace should instead be captured with Server Audit (which leverages Extended Events behind the scenes).<\/p>\n<p>Finally, don&#8217;t forget that if you&#8217;re looking to take an existing Trace and convert it to Extended Events, you can use <a href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/converting-sql-trace-to-extended-events-in-sql-server-2012\/\">Jonathan&#8217;s stored procedure<\/a> to get the event session DDL.<\/p>\n<p>Happy Extended Eventing!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Every time I talk about migrating from Profiler and Trace to Extended Events (XE), I seem to add something to my presentation.\u00a0 It&#8217;s not always intentional, and I don&#8217;t know if it&#8217;s because I just can&#8217;t stop tweaking my demos and contents, or something else, but here in London at IEPTO2 this week, I added [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Mapping Events from Trace to Extended Events - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Mapping Events from Trace to Extended Events - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-06-22T15:15:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-06-24T08:18:35+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/\",\"name\":\"Mapping Events from Trace to Extended Events - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-06-22T15:15:31+00:00\",\"dateModified\":\"2016-06-24T08:18:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Mapping Events from Trace to Extended Events\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Mapping Events from Trace to Extended Events - Erin Stellato","description":"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/","og_locale":"en_US","og_type":"article","og_title":"Mapping Events from Trace to Extended Events - Erin Stellato","og_description":"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/","og_site_name":"Erin Stellato","article_published_time":"2016-06-22T15:15:31+00:00","article_modified_time":"2016-06-24T08:18:35+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/","name":"Mapping Events from Trace to Extended Events - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-06-22T15:15:31+00:00","dateModified":"2016-06-24T08:18:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Looking to use to Extended Events instead of Trace? This post will help you with mapping events from Trace to the right ones in Extended Events","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/mapping-events-from-trace-to-extended-events\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Mapping Events from Trace to Extended Events"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/771"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=771"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/771\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=771"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=771"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=771"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}