I recently started using SSAS Server Traces a lot with SQL Server Analysis Services. This type of trace is basically the same trace you can create with SQL Server Profiler, but it runs without Profiler, uses less resources, and can be persisted across reboots. They’re a really handy tool.

I started using these when I built some AS monitoring tools based on the “Solution for Collecting Analysis Services Performance Data for Performance Analysis” sample on CodePlex. Seriously, totally revolutionized my life (at least the part related to administering complex AS installations and projects). After installing, adapting, and enhancing the functionality there I found I wanted more and easier ways to control AS traces, so I built some PowerShell functions to help manage them. These functions basically just wrap XMLA commands to make them easier to use.

I found that I wanted a convenient way to see what traces were running on a server, create them, delete them, and flush them (i.e., close out the current trace and create a new one, so you can process or otherwise work with events that were just logged). I have included two versions of my library in this sample. This first (SsasTraceLibrary.ps1) runs with PowerShell V1. The second (SsasTraceV2Library.ps1) is basically identical, but uses function header and parameter functionality from PowerShell V2 CTP3. I keep the V1 version around to deploy to servers (more on this later), but load the V2 in my environment to take advantage of the examples, help, and all of the other V2 goodness. I would encourage you to go with the V2 version, as it includes easy to use descriptions, examples, and better parameter help.

The PowerShell is really just a wrapper around XMLA commands… it just makes it easier to use.

Using the SsasTraceLibrary.ps1 in the Dev Environment

I’ve found I use these functions a decent bit as part of my day to day operations. I have the following command in my Profile.ps1 to load all the script files ending with “Library.ps1” in a given directory… I store command libraries like SsasTraceLibrary.ps1 in this folder, so they’re automatically loaded when PowerShell starts.

Using the SsasTraceLibrary.ps1 in the Server Environment

I mentioned earlier that I also deploy this script to my various AS instances. I do this because various people need to work on the machine, and I want an easy (read: single click) way to do things like start/stop/flush the trace on the machine. This also makes it easy to automate these actions as part of an ETL or job.

I use a batch file with the following commands:

1: ECHO Setting System Variables

2: SET DATA_COLLECTION_PATH=[INSTALLDIR]

3: SET SSAS_TRACE_UNC=\\[OLAPSERVER]\[TRACE_FILE_SHARE_NAME]\[OLAPSERVER]_SsasPerformanceErrorMonitoringTrace.trc

The parameters encased in ‘[‘ and ‘]’ are replaced whenever the scripts are deployed to a server with variables specific to their environment. Someone can now just run one of the batch files to Start, Stop, or Flush a trace on the server. I also typically call the file to Flush the trace file as part of my Processing job, so I can immediately load the results into a database for analysis.

Performance

So a question that will always come up when running traces like this is the amount of overhead they require. And of course they require some, both in terms of CPU to log the events and Disk to write them. I’ve typically seen this to be in the single digits of CPU, and I always write to a location where there isn’t disk contention. You’ll of course want to test in your environment, but I haven’t seen a performance hit that makes the ROI of running these traces not worth it. If you’re concerned, you could consider turning them on/off as part of a scheduled job, or just running them on an as needed basis. Personally, I’ve seen a huge benefit from running them 24/7 as I capture detailed processing information (how long each step takes), query information (who is doing what, how bad, and how often) and error information (some errors that aren’t caught in any other logs are captured via traces).

Next Steps

Takes these libraries and modify to your heart’s content. I use a template in the scripts that is my standard, but you can replace it, add more, or whatever you want to do. You could also add a little bit better error handling if desired.

Conclusion

So, included here are some functions that will help you with some basic functionality around SSAS traces. Feel free to post back if you have any ideas for improvements or things that would be cool to do.