Hilmar Buchta

SQL Server 2005 | SQL Server 2008

Usually we’re using load testing tools as a basis for performance tuning or in order to give reliable hardware recommendations. You can find two approaches for load testing at the Codeplex website.

 

The ASLoadSim project uses Visual Studio 2005 Team Edition for Software Testers and provides a good toolkit for automatic load testing. But even if you’re not planning to use the Team Edition you may still want to have a look at the best practices document "SSAS Load Testing Best Practices" that is also included in this project’s download.

The other source for stress testing available from the Codeplex website are the ASCMD stress testing scripts, which are basically a set of MDX queries and Windows command files to execute the queries. This is a very simple but still useful approach to stress testing the server and monitor the behavior of the server under heavy load.

You still need to provide some test queries for both projects. While both projects support features for clearing the cache in order to get comparable results, only the ASLoadSim includes random variables for the query builder. You may ask why you should think about random elements in your test queries as you’ve already cleared the cache for each run. But without those random elements you’ll probably still have effects caused by the operation system IO cache or even by hardware caches built into the harddrive controllers or harddrives themselves.

So it’s a good idea to provide some sort of random selection for your load test queries and since MDX offers support for VBA functions like Rnd() it should not be too difficult to design queries that randomly select elements from dimensions.

A very easy way to do so, is to use the topcount function on elements that are sorted randomly. The following query does this:

with
set random_customers as topcount(order([Customer].[Customer].[Customer],vba!rnd(1)),10)

select {} on 0,
random_customers on 1

from [Adventure Works]

image

Each run of the query should return 10 random customers from our Adventure Works Customer dimension. If you’re only interested in a single random customer, it’s also not difficult. We just have to consider that

Int ((upperbound – lowerbound + 1) * Rnd() + lowerbound)

results in a random number between lowerbound and upperbound. Because our level and set indexing is zero based, we have lowerbound=0 and upperbound=count-1, which simplifies the above formula to

Int (count* rnd())

So, a query, returning just a random customer could look like this:

select {} on 0,
[Customer].[Customer].[Customer].item(
vba!rnd() * [Customer].[Customer].[Customer].Count
)

on 1
from [Adventure Works]

Of course, in order to build your load test queries, you would include some other attributes, measures and calculated measures as well, but the samples above demonstrate how you can randomly pick dimension members.