I’ve created a simple query performance logging tool for Analysis Services, called the SSAS Performance Logger. The tool allows you to navigate through the metadata for a tabular model and select from measures and table attributes to build a simple query which is executed and timed. The query can be executed many times to get an accurate sampling of test variations and to test performance while various loads are placed on the server.
I literally created this over the weekend and plan to add additional capabilities as time allows – so please check back. To provide feedback, add comments to this post.
My initial objective was to choose two different measures that were alternate methods of calculating the same value, run them side-by-side and then see which performed best. Then it occurred to me that we can run any number of measure performance tests in iterative loops and compare the results by playing back the captured log results. Since the results are captured in a log database, the test results for each measure or query can easily be compared and analyzed using tools like Excel and Reporting Services.
One of my main objectives for a future version of the tool is to add logging for server performance counters like memory usage, thread counts and CPU capacity; while these queries are running.
What you will need:
I’ve developed SSAS Performance Logger for use with SSAS Tabular in SQL Server 2012. To manage the logging database, it uses the OLEDB data provider for SQL Server 2012 which may be backward compatible as far back as SQL Server 2008. It also uses the ADOOMD data provider version for Analysis Services. When MDX support is added to a future version, it should support SSAS sources as far back as SSAS 2008.
System requirements:
- Microsoft .NET Framework 4.5
- ADOMD 6.0 (can be installed with the SQL Server 2012 SP1 Feature Pack)
- An instance of SQL Server 2008 or better with administrative/create database rights for the logging database
With these components installed, you should be able to copy the executable to a folder and run it.
To install:
- Download the zip file and extract it to a folder on your system
- Verify that the required dependent components are installed
- Run SSAS Perf Tester.exe
The first time it runs, the application will check for the logging database and prompt you to create it
- On the SSAS Query page, enter the Analysis Services server or instance name and press Enter
- Select a model or perspective, measure and table/attribute from the drop-down list boxes to build the query
- Click the Start button to run the query once and see how long it took to run
- On the Options page, type or select the number of times to repeat the query
- Click the Start button the run the query in a loop. The results are displayed in a line chart showing the duration in milliseconds for each execution
Every query execution is logged for later analysis. Here is a view of the logItem table in the SSASPerformanceTest database:
Features I hope to add soon:
- Generate MDX queries in a addition to DAX
- Handle multiple measures in one testing batch
- Log server performance counters
very nice, Paul! thanks for your contribution to our community!! keep up the great job!!!
very cool! definitely like the logging capabilities. have you considered adding capabilities to simulate a concurrent workload (similar to AS Performance Workbench) ?
Thanks, Bill. I have considered this but my goal is to keep this tool simple and focused on optimizing measure an query design rather than server loading. However, you can easily run multiple instances of the tool in parallel. I just added support to capture sessions and user credentials which should provide more flexibility.
Nice, should maybe consider getting this up on CodePlex, appreciate you sharing and looking forward to testing it out.
Good suggestion, Dan. I’m not quite ready to put my spaghetti code out there for all the world to see yet.