Extended Events for Analysis Services

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.

[sql]
SELECT *
,CAST(event_data AS XML) AS ‘event_data_XML’
FROM sys.fn_xe_file_target_read_file(
‘D:\demo*.xel’
,NULL
,NULL
,NULL
)
[/sql]

Additional Resources

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).