SQL Centre of Excellence

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


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

blog comments powered by Disqus

Page List

Page List