Tracing Analysis Services (SSAS) with Extended Events – Yes it works and this is how
or: “Hasta la vista, Profiler”… 😉
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 – just like it was in the “old days” with SQL Server 2008/ 2008 R2, until 2012 brought the GUI.
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 not really working (“Use SQL Server Extended Events (XEvents) to Monitor Analysis Services
http://msdn.microsoft.com/en-us/library/gg492139.aspx”), I will show a quick example here.
The following code creates a session to collect the deadlocks events from the Analysis Services Instance:
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.
The package containing the events is “AS“, whereas targets come from our well known “Package0“. The one familiar with XEvents will know, what this implicates – more maybe in a later post.
Via the internal system view $system.discover_traces, we can see the active traces on the instance: the “FlightRecorder” which is still using the old-style Tracing technology (I wonder when Microsoft will add a new one just like system_health in SQL Server) and my sample session. You will also note, that the XEvent session’s trace file name is not visible here.
To access the collected data one can easily stop and delete the session by name as follows:
The collected data can be viewed, aggregated and filtered as normal with the Extended Events Viewer in Management Studio.
Here a sample with query runtimes:
In the detail pane on the bottom you can notice, that I turned on causality tracking here. Hence the activity ID /GUID correlate activity.
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.
I do expect some more GUI-support for Managing XEvents in Analysis Services in the next major release.
Have fun playing around with the sample. 🙂
From now on there is no excuse any more, to burden an Analysis Server that is already on its knees with Profiler…
“Hasta la vista, Profiler” 😉
Hopefully by MCM buddy and friend Reeves Smith will soon write his promised post on Tracing Analysis Services, maybe with a Performance Comparison.
”So, now you have to get going, Reeves!” 😀
Meanwhile I’d like to refer you to this article from another fellow MCM, Jonathan Kehayas, where you can see the enormous difference in terms of negative performance-impact of tracing via Profiler and SQL Trace vs XEvents:
Update: I conducted an excessive benchmarking on Extended Events and SQL Trace & Profiler myself now. The results are now public and can be found here:
Note: In the first half year 2013 there will be a second date for the Master-Class Seminars on Extended Events (13 and 14 June).
The next opportunity will be on 22 Nov. and 25 Nov. 2013!