Testing stored procedure scalability

Question

I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.

I'm trying to slam the DB to see what the breaking point (# of requests per second) would be.

In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app.

How can I make this a good test to achieve the test results based on SQL performance?

Is there a tool for this that i can give it a stored proc name and param for it to pund my DB?

I want to see if the DB can return a min. of 250 responses per second.

Answer 1

SQLQueryStress is a great tool for small scale concurrency testing but it isn't really up to the job of load testing. A surprisingly little known toolset is available for free, from Microsoft, that can cope with most SQL Server stress testing scenarios, the RML Utilities.

A very brief outline of the tools:

• ReadTrace converts Profiler traces from .trc files to .rml (Replay Markup Language).
• OStress is used to replay .rml and .sql files against a server.
• ORCA (OStress Replay Control Agent) co-ordinates replay across distributed instances of OStress.
• Reporter provides performance analysis reports.

There is an excellent fast start article from the SQLCat team which includes a sample database and workloads, Precision Performance for Microsoft SQL Server using RML Utilities 9.0

You have the option of either Profiling activity for replay or, possibly more appropriate in your scenario, hand cranking a set of .sql scripts that you then replay via OStress and ORCA.

Great tools for both load testing and investigating performance issues.

Answer 2

One tool you can use is SQLQueryStress. For a quick start provide: the stored procedure the iterations and the number of threads. Then start the stress testing. Metrics like CPU seconds, logical reads, etc... will be shown.

Answer 3

Sounds like you are a .NET developer. One way would be to write a small application that uses multithreading and have just a set amount of concurrent hits to the database and the stored procedure. Run a trace while this is happening.

Write the application code to increment the amount of concurrent threads on a specific interval if you want to automatically increase the load on SQL Server.

That's the way I would go about it.