When evaluating load test tools for Analysis Services, there are number of tools available on codeplex or you can roll your own using a load testing suite such as Visual Studio Test Edition.
What do you need out of a load test tool ? Here is a list of qualities you may want to consider when selecting tool(s):
| Quality | Description |
| Easy to Get Started | The toolset should have enough to get you up and running quickly without writing buckets of code or even worse, custom XML files. |
| Capture Trace | the toolset should support automatically capturing a trace file, suitable for replay. |
| Work With Parameters | The tool should deal with parameterised queries (reporting services), both from a capture point of view and a replay perspective. |
| Folder playback | Does the tool work with queries saved into a folder (pretty basic and messy) |
| Table playback | Does the tool support loading queries direct from a trace sql table (nicer) |
| Query Replay | Does the allow replay of queries |
| Trace Replay | Can the tool play back a trace file, or trace table |
| Multi –Threaded | Can the tool playback on multiple threads |
| Distributed Load | Can the tool use multiple agent to playback |
| Distributed Replay | Can the tool replay a trace file using a distributed method |
| NLB Simulation | Can the tool simulate multiple Analysis Servers in farm |
| Capture performance counters | Can the tool automatically capture relevant performance counters from analysis services and windows |
| Chart performance counters | Can the tool collate and chart counters to help identify bottlenecks. |
| Performance Thresholding | Can the tool automatically identify known issues with performance counters |
| Store Results | Can the tool label and store results so they can be retrieved at a later date. |
| Reporting Tools | Can the tool produce reports and charts on performance |
| Regression Testing | Can the tool compare a baseline to other runs and identify regression issues. |
| Functional/Upgrade Testing | Can the tool verify that two environments produce the exact same output. |
| Reporting Services | Can the tool also load test reporting services or web sites for end to end load testing. |
I quickly looked at a few of the toolsets available
| Toolset | Description |
| ASCMD for stress testing http://sqlsrvanalysissrvcs.codeplex.com/ | Great for automating capture, but replay is basic enough and does not scale to distributed or offer performance counter capture / analysis by itself |
| AS LoadSim http://www.beeii.com/?p=466 http://sqlsrvanalysissrvcs.codeplex.com/ | Written by Microsoft Consulting Services, this toolset uses Visual Studio to load test from a custom XML format which can be generated using the “AS Query Generator” |
| AS Performance Workbench http://asperfwb.codeplex.com/ | Assuming you have sample queries in a folder, this toolset replays queries, captures and shows performance graphs and produces reports. very easy to setup and use. |
| SQL Profiler | Great for capturing and replay of workloads, although does not support distributed replay for MDX – even in SQL 2012. |
| Custom VS 2010 Load Test | Roll your own code, not as hard as you think and more flexible. Well be publishing our own solution soon , after SQL Bits X http://sqlbits.com/Sessions/Event10/Load_Testing_Analysis_Services |
So how do these tools stack up on features. A rough comparison is below:
the conclusion – If you want a basic test then AS Performance Workbench combined with using Profiler or ASCMD to help capture a workload will get you started faster.
If you want something a lot more scalable or more sophisticated with regression or functional testing, then using a Visual Studio 2010 load test is going to suite better. I’ll publish some sample code to help get started with the feature matrix as above
