{"id":2889,"date":"2013-11-21T17:08:49","date_gmt":"2013-11-21T16:08:49","guid":{"rendered":"http:\/\/andreas-wolter.com\/extended-events-vs-sql-trace-im-vergleich\/"},"modified":"2018-07-17T14:51:11","modified_gmt":"2018-07-17T13:51:11","slug":"extended-events-vs-sql-trace","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/extended-events-vs-sql-trace\/","title":{"rendered":"Comparing Extended Events vs SQL Trace \u2013 or why SQL Trace &#038; Profiler are just a thing of the past :-)"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-535d76525643ccc0d2bac01fc93bf543\">\n#top .av-special-heading.av-av_heading-535d76525643ccc0d2bac01fc93bf543{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-535d76525643ccc0d2bac01fc93bf543 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-535d76525643ccc0d2bac01fc93bf543 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-535d76525643ccc0d2bac01fc93bf543 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >Extended Events vs SQL Trace <\/h3><div class='av-subheading av-subheading_below'><p>\u2013 or why SQL Trace &#038; Profiler are just a thing of the past \ud83d\ude42<\/p>\n<\/div><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>As a reminder: Extended Events have been <strong>integrated in SQL Server since SQL Server 2008<\/strong>. And since SQL Server 2012 SP1, all events existing in SQL Trace have been available. In addition, Extended Events have also been available for Analysis Services since SQL Server 2012 <a href=\"https:\/\/andreas-wolter.com\/en\/tracing-analysis-services-ssas-with-extended-events-yes-it-works-and-this-is-how\/\">Tracing Analysis Services (SSAS) with Extended Events \u2013 Yes it works and this is how<\/a>).<!--more--><\/p>\n<p>For those of you who are still working with the old tool <em>SQL Server Profiler<\/em> (Profiler is the frontend for <em>SQL Trace<\/em>, started with <em>sp_trace_create<\/em>) and have not quite been able to decide for the new technology, here is some decision guidance.<\/p>\n<p>What Extended Events (XEvents) do better than SQL Trace:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><ul>\n<li>The only possibility of <strong>Tracing<\/strong> <strong>new features<\/strong> like FileStream, FileTable, AlwaysOn, ColumnStore, Hekaton\/XTP etc.<\/li>\n<li><strong>Many more Events<\/strong> traceable, even for older releases (see *1, *2 below)<\/li>\n<li>Significantly <strong>less Observer-Overhead<\/strong>, also see: <a href=\"https:\/\/andreas-wolter.com\/en\/performance-overhead-of-tracing-with-extended-event-targets-vs-sql-trace-under-cpu-load\/\">Performance overhead of tracing with Extended Event targets vs SQL Trace under CPU Load<\/a><\/li>\n<li>Performance\/Overhead can be configured and tuned<\/li>\n<li>Events and Filters can be <strong>adapted live<\/strong> \u2013 meaning during active session<\/li>\n<li>Official <strong>2 \u00b5s\/Event<\/strong> vs. 4ms\/Event in SQLTrace<\/li>\n<li>Event-loss is configurable<\/li>\n<li><strong>Efficient filtering<\/strong> through architecture<\/li>\n<li><strong>Complex predicates<\/strong> such as <em>Counter<\/em> or <em>last_error<\/em>, <em>less_than_min_datatype<\/em> oder <em>greater_than_max_datatype<\/em><\/li>\n<li><strong>Correlation of Events <\/strong>possible<\/li>\n<li>Possibility of following <strong>Events from Client into the Database<\/strong><\/li>\n<li>Easy Automation<\/li>\n<li>Directly integrated in Management Studio<\/li>\n<li>Many <strong>analysis directly inside the GUI<\/strong> possible (in order to improve them, <strong>please vote<\/strong> at Microsoft Connect: <a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/details\/798731\/extended-events-ui-export-display-settings-include-grouping\" target=\"_blank\" rel=\"noopener\">Extended Events UI Export Display Settings: include grouping<\/a>)<\/li>\n<li><strong><em>Query_hash <\/em><\/strong><strong>for identification<\/strong> of identical queries available<\/li>\n<li>No <strong>10 clicks to set up<\/strong> a simple session including filter<\/li>\n<li>Choice of <strong>different destinations for storing <\/strong>EventData (6)<\/li>\n<li>\u201cGoal-oriented\u201d destinations such as <strong>Counter and Histogram<\/strong><\/li>\n<li><strong>Multiple destinations<\/strong> can be combined for \u201c<em>On the fly \u2013 top-down analyses<\/em>\u201d<\/li>\n<li>Possibilities for entirely new <strong>insights into internal matters<\/strong> of database engine (Latching, Spinlocks, Multi-victim-Deadlock, Wait_Infos per session\/query, Caching-processes, Ghost-cleanup, analysis of Page Splits, Page-Compression processes, to name just a few)<\/li>\n<li><strong>Stack Tracing of a single process<\/strong> possible \u2013 instead of a complete Server Dump<\/li>\n<li>Definition with standard <strong>DDL-Statements<\/strong><\/li>\n<li><strong>API<\/strong> for integration into one\u2019s own tools available<\/li>\n<li><strong>PowerShell<\/strong> support<\/li>\n<li>Last but not least: Finally a good reason to learn <strong>XML &#038; XQuery<\/strong>? \ud83d\ude09<\/li>\n<\/ul>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>*1<\/p>\n<p><strong>Extended Events vs. SQL trace Events per Version<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-3083\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1.png\" alt=\"\" width=\"850\" height=\"291\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1.png 850w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1-600x205.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1-300x103.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1-768x263.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1-705x241.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/11\/1710_Extended_Events_per_SQL_Server_Version-1-450x154.png 450w\" sizes=\"auto, (max-width: 850px) 100vw, 850px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>*2<\/p>\n<p>As an example:<\/p>\n<p>For <strong>Service Broker<\/strong> there are: <strong>15<\/strong> <strong>Events<\/strong> in SQLTrace vs. <strong>44<\/strong> <strong>Events<\/strong> in XEvents (SQL Server 2012 SP1)<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>I hope this helps some of you to unlearn the old habit and accept the little learning phase.<\/p>\n<p>A list of all Extended Events in SQL Server 2012 SP1 including its counter piece in SQL Trace that can be used for migration purposes (SQLTrace-> XEvent Trace) is available on <a href=\"http:\/\/www.andreas-wolter.com\/public\/sql-server-resources\/1311_Mapping_Extended_Events_to_SQLTrace.htm\">this page<\/a>. (Due to size restriction, it didn\u2019t fit in here anymore.)<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><a href=\"http:\/\/www.andreas-wolter.com\/public\/sql-server-resources\/1311_Mapping_Extended_Events_to_SQLTrace.htm\">Mapping Extended Events with <em>sys.trace_xe_event_map<\/em> to SQL Trace<\/a><\/p>\n<p>Happy better Tracing<\/p>\n<p>Andreas<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>P.S. For 2014, the next round of training in this topic is being developed in conjunction with <a href=\"http:\/\/www.sarpedonqualitylab.com\/SQL_Master-Classes.htm\" target=\"_blank\" rel=\"noopener\">SQL Server Master-Classes on the topic \u201c<strong>Tracing with Extended Events<\/strong>\u201d<\/a> \ud83d\ude42<\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-8  el_after_av_textblock  el_before_av_one_full '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-9  el_after_av_hr  el_before_av_social_share  first flex_column_div av-zero-column-padding  '     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-11  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/extended-events-vs-sql-trace\/&#038;t=Comparing%20Extended%20Events%20vs%20SQL%20Trace%20%E2%80%93%20or%20why%20SQL%20Trace%20%26%20Profiler%20are%20just%20a%20thing%20of%20the%20past%20%3A-%29\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Comparing%20Extended%20Events%20vs%20SQL%20Trace%20%E2%80%93%20or%20why%20SQL%20Trace%20%26%20Profiler%20are%20just%20a%20thing%20of%20the%20past%20%3A-%29&#038;url=https:\/\/andreas-wolter.com\/en\/?p=2889\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Comparing%20Extended%20Events%20vs%20SQL%20Trace%20%E2%80%93%20or%20why%20SQL%20Trace%20%26%20Profiler%20are%20just%20a%20thing%20of%20the%20past%20%3A-%29&#038;url=https:\/\/andreas-wolter.com\/en\/extended-events-vs-sql-trace\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-12  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"As a reminder: Extended Events have been integrated in SQL Server since SQL Server 2008. And since SQL Server 2012 SP1, all events existing in SQL Trace have been available. In addition, Extended Events have also been available for Analysis Services since SQL Server 2012 Tracing Analysis Services (SSAS) with Extended Events \u2013 Yes it [&hellip;]","protected":false},"author":4,"featured_media":3083,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55,56],"tags":[18,175],"class_list":["post-2889","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-extended-events-en","category-tracing-monitoring-en","tag-extended-events-en","tag-sql-trace_profiler-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2889","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=2889"}],"version-history":[{"count":8,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2889\/revisions"}],"predecessor-version":[{"id":2890,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2889\/revisions\/2890"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/3083"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=2889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=2889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=2889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}