Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Great question. I would expand it to be a bit more general and ask: How do you load test your database? –  Nick Chammas Sep 15 '11 at 18:10
add comment

3 Answers 3

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Why don't you write this app making use of my open source project Benchmark.NET and using a parallel benchmark? –  Andrei Rinea Mar 19 '12 at 16:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.