SQL Server 2008: The reasonable stress tests scenario

I am performing stress testing on SQL Server 2008 with JMeter.

I wish to improve a stored procedure that has to serve 20 requests per second.

The procedure takes an xml parameter and re开发者_如何学Pythonturns an xml result.

Should I use only one parameter value or test multiple scenarios?

My main doubts are:

1. recompilations of the procedure execution plan (this may slow down the procedure)
2. extraction of data from disk (not all necessary data may be hold in the main memory)

Designing a realistic Stress Test/Load Test in SQL Server is an art.

There are many factors that can impact performance:

• Hardware: You need to run your tests against the the same hardware that you have defined your target (20 call per second). This includes disk configuration, redundancy, clustering, ... This is not always possible so you need to make it as close as possible however the more different your test environment becomes, the more unrealistic results can be. This means, for example, if you use 2 CPUs instead of 4, you cannot adjust the parameters accordingly.

• Data load: in terms of number of the records you need to test, it is ideal to have around 30%-40% more of the maximum rows you expect in the tables.

• Data and index distribution: It is a common mistake to load the server with a preset or completely random data. Both are wrong. The distribution of the values need to be realistic. For example distribution of the marital status is not the same across all possible values so you need to design your data generation to include this.

• Index fragmentation: this is a tough one. Normally indexes are rebuilt overnight, but during the course of the day, indexes become fragmented so the performance can be very different during those times.

• Concurrent load: A server could provide you with 20 requests per second, if it is the only call you are making to the database but as soon as you start making other calls, it all falls to pieces. The load need to include other related parts of the system.

• Operation Load: It is absolutely no point to make 20 calls per second if the requests are all the same. You need to use Data Generation techniques to make the requests realistic not purely random.

If you are using C#, I have done this tool a while back which might help you with creating realistic random data.