This year I’ve spent quite a bit of time tracing Analysis Services events. In the lab, my goto has always been SQL Profiler for it’s mere simplicity. At clients, I usually take the extra step of creating a server side trace (example) to reduce the “observer overhead”. Both of these options have served me well. But now that SQL Profiler has been deprecated in SQL2012, it’s time to start familiarizing myself with a shiny new hammer: Extended Events.
Based on what I’ve read/seen so far, the extended events architecture looks very promising…more flexible, extensible, and way more scalable (due to its very low observer overhead) than profiler.
Going forward, I’ve decided to make a concerted effort to use Extended Events (instead of Profiler) for all my SSAS tracing activities (including basic lab/demo stuff) to help build some proficiency. So far…day 4…it hasn’t been *that* bad. The biggest hurdle is getting used to scripting every trace…there’s no UI for SSAS extended event tracing. So to help make that a bit easier I went ahead and put together 2 xmla templates that can be used to create and tear-down SSAS extended event traces.
Create Extended Event Trace
This template is structured to resemble the familiar SQL Profiler event category layout. All you have to do is uncomment the events you want to capture, give the trace a name/id, set the target and execute. You can download a copy of the script here.
Delete Extended Event Trace
This one is very straight forward…it simply deletes the trace referenced by trace id. You can download a copy from here.
I also went ahead and added these script templates to the rest of my SSMS templates for Analysis Services…using the same steps outlined in this post. The easier it is, the more likely I am to stick to it 🙂
The output is an XEL file which you can view in management studio. However, a more useful method for reviewing larger traces is the sys.fn_xe_file_target_read_file system function…be prepared to knock some dust off those XML shredding skills.
,CAST(event_data AS XML) AS ‘event_data_XML’
Chris Webb (b | t) has one of the only tutorials available (here) for using Extended Events with Analysis Services…and from the looks of it he had to claw his way to a solution via the help of a few other SSAS-jedi.
If you want a good primer on Extended Events, I highly recommend you watch this SQLBits X session by Bob Beauchemin (b | t). The presentation focuses on SQL Server Database Engine but the architecture should be very similar for Analysis Services – try not to get jealous of the UI 😉
Jonathan Kehayias (b | t) is another SQL Server guru who’s published a lot of information on Extended Events. Both Jonathan and Bob work for SQLSkills so you can rest assured they know what they’re talking.
Update 20140502: you can specify additional parameters in the target section of the XMLA command to control things like max file size and max number of rollover files (complete list).
16 replies on “Extended Events for Analysis Services”
Really helped me!
[…] Bill Anton (Blog|Twitter) – Extended Events For Analysis Services […]
By default, the extended events generate multiple files each 1GB in size, totaling 5GB. Is there a way to control this file size? I would like smaller files being generated, making it faster to read those using sys.fn_xe_file_target_read_file on a schedule.
Great question! See updated section at bottom of the post.
This is amazing! Thanks a ton Bill!
[…] and most important, you can read this great article from Bill Anton where you can download a ready made XMLA script where there are listed all the relevant Aanalysis […]
[…] example of a small trace I let run on the SSAS server for a couple of minutes. I did borrow Bill Anton's script as a starting point for this example. Both Mark Vallincourt and Bill […]
[…] go over how to use Extended Events in SSAS because the following blog posts do a great job already: http://byobi.com/2013/06/extended-events-for-analysis-services/ http://markvsql.com/2014/02/introduction-to-analysis-services-extended-events/ […]
[…] Bill Anton (b | t) – Extended Events For Analysis Services […]
I am trying to generate usage of attributes and measures. For this, I need to parse ‘TextData’. Is there any way to parse and get this information?
are you referring to the subcube event with text data values like “101101000011011000001” ?
this -> http://byobi.com/2014/04/olap-query-log-and-subcube-vectors/
if so – here’s the only (non-manual) method I’ve seen…
[…] to create, start and stop a trace session you have to use XMLA commands. There are plenty of blog posts out there explaining how to do this but it’s still incredibly time-consuming and fiddly to do. […]
[…] written about extended events for Analysis Services quite a bit (here, here, here, here, here) because I think they’re super helpful and very important in […]
[…] the query workload using an xEvents (basic example, autorestart […]
great article but on a SSAS server that does not have SQL Server is there a way to schedule the extended event
Hi Tim – glad you liked it. The easiest way is probably to just set the AutoRestart option in the XMLA command when creating the trace. I know it works for 2014 and above… but not sure about older versions. As long as you’re not capturing super-large (e.g. DAX Query Plan) or verbose events (e.g. subcube) it should be fine to leave it running 24×7