Load Test Tools for Analysis Services

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

Easy to Get StartedThe toolset should have enough to get you up and running quickly without writing buckets of code or even worse, custom XML files.
Capture Tracethe toolset should support automatically capturing a trace file, suitable for replay.
Work With ParametersThe tool should deal with parameterised queries (reporting services), both from a capture point of view and a replay perspective.
Folder playbackDoes the tool work with queries saved into a folder (pretty basic and messy)
Table playbackDoes the tool support loading queries direct from a trace sql table (nicer)
Query ReplayDoes the allow replay of queries
Trace ReplayCan the tool play back a trace file, or trace table
Multi –ThreadedCan the tool playback on multiple threads
Distributed LoadCan the tool use multiple agent to playback
Distributed ReplayCan the tool replay a trace file using a distributed method
NLB SimulationCan the tool simulate multiple Analysis Servers in farm
Capture performance countersCan the tool automatically capture relevant performance counters from analysis services and windows
Chart performance countersCan the tool collate and chart counters to help identify bottlenecks.
Performance ThresholdingCan the tool automatically identify known issues with performance counters
Store ResultsCan the tool label and store results so they can be retrieved at a later date.
Reporting ToolsCan the tool produce reports and charts on performance
Regression TestingCan the tool compare a baseline to other runs and identify regression issues.
Functional/Upgrade TestingCan the tool verify that two environments produce the exact same output.
Reporting ServicesCan 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

ASCMD for stress testing
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
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
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 ProfilerGreat for capturing and replay of workloads, although does not support distributed replay for MDX – even in SQL 2012.
Custom VS 2010 Load TestRoll 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

Leave a Reply