The MSDN documentation and SSMS support for Analysis Services xEvents is severely lacking when compared to the database engine. In fact there wasn’t even a GUI for SSAS xEvents until SQL 2016… which meant anyone who wanted to create an xEvents trace had to craft another cryptic XMLA command. To make matters worse, only the most basic options have been included in the MSDN documentation. So in order to know about and use some of the additional options (which are very very helpful) you had to scour the interwebs hoping someone from the community had discovered and shared something in a blog post or video.
Having spent the past few years focusing on SSAS – a big portion of which has been specifically dealing with performance (monitoring, optimization, tuning) – I was pleasantly surprised to learn something new this week involving Extended Events…so surprised (and excited) that I felt the need to stop the clock on my current client project and write this blog post
There’s an undocumented (and probably unsupported) option for creating an Analysis Services xEvents trace that automatically restarts anytime the server is rebooted or the Analysis Services service is restarted.
Here’s how to use it…
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <ObjectDefinition> <Trace> <AutoRestart>true</AutoRestart> <ID>TraceQuery</ID> <Name>TraceQuery</Name> <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300"> <event_session name="TraceQuery" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <event package="AS" name="QueryEnd" /> <target package="package0" name="event_file"> <parameter name="filename" value="C:\Program Files\Microsoft SQL Server\MSAS13.SSAS_TAB_2014\OLAP\Log\TraceQuery.xel" /> <parameter name="max_file_size" value="32" /> </target> </event_session> </XEvent> </Trace> </ObjectDefinition> </Create>
After running that command (be sure to adapt the filename parameter to fit your implementation) you can query the $system.DISCOVER_TRACES dynamic management view to confirm the AutoRestart property is set correctly…
Then you can reboot the server and/or restart the SSAS service and confirm that the trace started back up.
Why is this Important?
I’ve 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 managing/monitoring an Analysis Services environment.
At the very least… we’re talking bare-minimum… you need to be capturing (in a sql table) the text and duration of every. single. query. PERIOD!
When this topic comes up in client conversations, it quickly leads into the classic build-vs-buy decision/discussion… and despite my strong opinion that buying an off-the-shelf solution from companies like IDERA or SQL Sentry is the smarter choice for most clients, there are still many who would prefer to roll-their-own.
Roll-Your-Own SSAS Monitoring
If for whatever reason, you opt not to purchase an off-the-shelf monitoring solution and instead decide to roll-your-own, the framework is quite simple.
- Capture SSAS activity in an Extended Events (xEvents) trace
- Load data from xEvent trace file(s) into SQL table
- Run queries against SQL table to review performance (e.g. query duration, processing duration, etc)
Note: Of course I’m over-simplifying this a bit. If you want to get a little crazy, you can throw in some performance monitor (perfmon) trace data and start correlating server resource availability (or lack of) with SSAS activity. Yes, it gets quite a bit more complex which is the main reason I prefer off-the-shelf.
One key property of a monitoring solution is that it must always be collecting data. Any activity that occurs while the trace is NOT running will be missing from the SQL table and review.
So how do we handle the scenario where the server is rebooted?
- Option 1: always remember to restart the trace after server reboots
- Option 2: create a SQL Agent job to poll for the SSAS service status and start the xEvent trace if its not already running
- Option 3: write a custom .NET watchdog service to poll for the SSAS service status and start the xEvents trace if its not already running
Those are the options I’ve used or seen used in the past… and to be sure, all of them have their drawbacks in reliability and/or complexity.
…which is why I was so excited when it was brought to my attention that there is an “AutoRestart” option for SSAS xEvents!
If you’re interested in learning more about how to use xEvents to pinpoint and troubleshoot Analysis Services performance issues, you won’t want to miss the 3-hour session at PASS Summit 2016 that Thomas LeBlanc (b | t) and I will be delivering out in Seattle: Troubleshooting Analysis Services Performance Issues. If that’s too long to wait, feel free to drop me an email.