{"id":2960,"date":"2013-04-09T15:24:07","date_gmt":"2013-04-09T14:24:07","guid":{"rendered":"http:\/\/andreas-wolter.com\/?p=2960"},"modified":"2017-10-09T18:30:44","modified_gmt":"2017-10-09T17:30:44","slug":"tracing-analysis-services-ssas-with-extended-events-yes-it-works-and-this-is-how","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/tracing-analysis-services-ssas-with-extended-events-yes-it-works-and-this-is-how\/","title":{"rendered":"Tracing Analysis Services (SSAS) with Extended Events \u2013 Yes it works and this is how"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-040f3309c7c0146834be905b65116236\">\n#top .av-special-heading.av-av_heading-040f3309c7c0146834be905b65116236{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-040f3309c7c0146834be905b65116236 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-040f3309c7c0146834be905b65116236 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-040f3309c7c0146834be905b65116236 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\"  >Tracing Analysis Services (SSAS) with Extended Events \u2013 Yes it works and this is how<\/h3><div class='av-subheading av-subheading_below'><p>or: \u201cHasta la vista, Profiler\u201d&#8230; \ud83d\ude09<\/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>One of the features new in Analysis Services 2012 is the support of the SQL Server Extended Events Framework.<\/p>\n<p>While there is no GUI support for that, yet, it is however possible to set up a XEvent session via DDL commands &#8211; just like it was in the \u201cold days\u201d with SQL Server 2008\/ 2008 R2, until 2012 brought the GUI.<!--more--><\/p>\n<p>Since I have been asked a lot at my sessions on Extended Events on how it is done in Analysis Services, and the Books Online sample code is\u00a0not really working (\u201cUse SQL Server Extended Events (XEvents) to Monitor Analysis Services<br \/>\n<a title=\"http:\/\/msdn.microsoft.com\/en-us\/library\/gg492139.aspx\" href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/gg492139.aspx\" target=\"_blank\" rel=\"noopener\">http:\/\/msdn.microsoft.com\/en-us\/library\/gg492139.aspx<\/a>\u201d), I will show a quick example here.<\/p>\n<p>The following code creates a session to collect the deadlocks events from the Analysis Services Instance:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-b1d7b82d55e1bcf3a2f0f576a1917483\">\n.avia-image-container.av-av_image-b1d7b82d55e1bcf3a2f0f576a1917483 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-b1d7b82d55e1bcf3a2f0f576a1917483 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-b1d7b82d55e1bcf3a2f0f576a1917483 av-styling- avia-align-center  avia-builder-el-2  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2994 avia-img-lazy-loading-not-2994 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents.png\" alt='' title='Code_deadlockevents'  height=\"447\" width=\"1031\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents.png 1031w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-600x260.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-300x130.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-768x333.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-1030x447.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-705x306.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/Code_deadlockevents-450x195.png 450w\" sizes=\"(max-width: 1031px) 100vw, 1031px\" \/><\/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 one can see, the definition like session configuration and targets, is quite similar to SQL Server, since it is in fact based on the same architecture.<br \/>\nThe package containing the events is &#8220;<em><strong>AS<\/strong><\/em>&#8220;, whereas targets come from our well known &#8220;<strong><em>Package0<\/em><\/strong>&#8220;. The one familiar with XEvents will know, what this implicates &#8211; more maybe in a later post.<\/p>\n<p>Via the internal system view <em>$system.discover_traces<\/em>, we can see the active traces on the instance: the \u201cFlightRecorder\u201d which is still using the old-style Tracing technology (I wonder when Microsoft will add a new one just like <em>system_health<\/em> in SQL Server) and my sample session. You will also note, that the XEvent session\u2019s trace file name is not visible here.<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-eed9f1c1cee8068153dd209c09113918\">\n.avia-image-container.av-av_image-eed9f1c1cee8068153dd209c09113918 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-eed9f1c1cee8068153dd209c09113918 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-eed9f1c1cee8068153dd209c09113918 av-styling- avia-align-center  avia-builder-el-4  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2962 avia-img-lazy-loading-not-2962 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions.png\" alt='' title='1304_TraceSessions'  height=\"75\" width=\"905\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions.png 905w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions-600x50.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions-300x25.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions-768x64.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions-705x58.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_TraceSessions-450x37.png 450w\" sizes=\"(max-width: 905px) 100vw, 905px\" \/><\/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>To access the collected data one can easily stop and delete the session by name as follows:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-6f144158437da50df54be899b2eae839\">\n.avia-image-container.av-av_image-6f144158437da50df54be899b2eae839 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-6f144158437da50df54be899b2eae839 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-6f144158437da50df54be899b2eae839 av-styling- avia-align-center  avia-builder-el-6  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2996 avia-img-lazy-loading-not-2996 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/code_endsession.png\" alt='' title='code_endsession'  height=\"153\" width=\"667\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/code_endsession.png 667w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/code_endsession-600x138.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/code_endsession-300x69.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2013\/04\/code_endsession-450x103.png 450w\" sizes=\"(max-width: 667px) 100vw, 667px\" \/><\/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>The collected data can be viewed, aggregated and filtered as normal with the Extended Events Viewer in Management Studio.<br \/>\nHere a sample with query runtimes:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-b4c262079063bf022155d914d1276f39\">\n.avia-image-container.av-av_image-b4c262079063bf022155d914d1276f39 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-b4c262079063bf022155d914d1276f39 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-b4c262079063bf022155d914d1276f39 av-styling- avia-align-center  avia-builder-el-8  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2964 avia-img-lazy-loading-not-2964 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer.png\" alt='' title='1304_XEventViewer'  height=\"561\" width=\"1252\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer.png 1252w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-600x269.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-300x134.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-768x344.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-1030x462.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-705x316.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/10\/1304_XEventViewer-450x202.png 450w\" sizes=\"(max-width: 1252px) 100vw, 1252px\" \/><\/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>In the detail pane on the bottom you can notice, that I turned on <em>causality tracking<\/em> here. Hence the activity ID \/GUID correlate activity.<\/p>\n<p>So as you see, for a fact, the Analysis Services engine has been extended to be using the Extended Events architecture for better performing and more flexible Tracing.<br \/>\nI do expect some more GUI-support for Managing XEvents in Analysis Services in the next major release.<\/p>\n<p>Have fun playing around with the sample. \ud83d\ude42<\/p>\n<p>From now on there is no excuse any more, to burden an Analysis Server that is already on its knees with Profiler&#8230;<\/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\" ><h5 style=\"text-align: center;\">\u201cHasta la vista, Profiler\u201d \ud83d\ude09<\/h5>\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><em>Hopefully by MCM buddy and friend Reeves Smith will soon write his <strong>promised <\/strong>post on Tracing Analysis Services, maybe with a Performance Comparison.<\/em><br \/>\n<em>\u201dSo, now you have to get going, Reeves!\u201d \ud83d\ude00<\/em><\/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>Meanwhile I\u2019d like to refer you to this article\u00a0from another fellow MCM, Jonathan Kehayas, where you can see the enormous difference in terms of negative\u00a0performance-impact of tracing via Profiler and SQL Trace vs XEvents:<\/p>\n<p><a title=\"http:\/\/www.sqlperformance.com\/2012\/10\/sql-trace\/observer-overhead-trace-extended-events\" href=\"http:\/\/www.sqlperformance.com\/2012\/10\/sql-trace\/observer-overhead-trace-extended-events\" target=\"_blank\" rel=\"noopener\">www.sqlperformance.com\/2012\/10\/sql-trace\/observer-overhead-trace-extended-Events<\/a><\/p>\n<p>Update: I conducted an excessive benchmarking on Extended Events and SQL Trace &#038; Profiler myself now. The results are now public and can be found here:<\/p>\n<p><a href=\"https:\/\/andreas-wolter.com\/en\/performance-overhead-of-tracing-with-extended-event-targets-vs-sql-trace-under-cpu-load\/\">https:\/\/andreas-wolter.com\/en\/performance-overhead-of-tracing-with-extended-event-targets-vs-sql-trace-under-cpu-load\/<\/a><\/p>\n<p>Andreas<\/p>\n<p>Note: In the first half year 2013 there will be a second date for the Master-Class\u00a0Seminars on Extended Events (13 and 14 June).<\/p>\n<p>The next opportunity\u00a0will be on \u00a022 Nov. and 25 Nov. 2013!<\/p>\n<p><a title=\"http:\/\/www.sarpedonqualitylab.com\/SQL_Master-Classes.htm\" href=\"http:\/\/www.sarpedonqualitylab.com\/SQL_Master-Classes.htm\" target=\"_blank\" rel=\"noopener\">http:\/\/www.sarpedonqualitylab.com\/SQL_Master-Classes.htm<\/a><\/p>\n<\/div><\/section>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-13  el_after_av_textblock  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-14  el_after_av_hr  el_before_av_comments_list  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\/tracing-analysis-services-ssas-with-extended-events-yes-it-works-and-this-is-how\/&#038;t=Tracing%20Analysis%20Services%20%28SSAS%29%20with%20Extended%20Events%20%E2%80%93%20Yes%20it%20works%20and%20this%20is%20how\" 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=Tracing%20Analysis%20Services%20%28SSAS%29%20with%20Extended%20Events%20%E2%80%93%20Yes%20it%20works%20and%20this%20is%20how&#038;url=https:\/\/andreas-wolter.com\/en\/?p=2960\" 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=Tracing%20Analysis%20Services%20%28SSAS%29%20with%20Extended%20Events%20%E2%80%93%20Yes%20it%20works%20and%20this%20is%20how&#038;url=https:\/\/andreas-wolter.com\/en\/tracing-analysis-services-ssas-with-extended-events-yes-it-works-and-this-is-how\/\" 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<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":"One of the features new in Analysis Services 2012 is the support of the SQL Server Extended Events Framework. While there is no GUI support for that, yet, it is however possible to set up a XEvent session via DDL commands &#8211; just like it was in the \u201cold days\u201d with SQL Server 2008\/ 2008 [&hellip;]","protected":false},"author":4,"featured_media":2968,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[122,55,56],"tags":[190],"class_list":["post-2960","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analysis-services","category-extended-events-en","category-tracing-monitoring-en","tag-analysis-services"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2960","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=2960"}],"version-history":[{"count":15,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2960\/revisions"}],"predecessor-version":[{"id":3126,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2960\/revisions\/3126"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/2968"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=2960"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=2960"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=2960"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}