Testing stored procedure scalability

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.

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.

StackExchange Database Administrators Q#5809, answer score: 13