adam machanic now blogs at http://sqlblog.com

Synchronous testing

As a prerequisite for this blog, since I'm going to get into a lot of performance and scalability questions, I need some way for readers to run scripts synchronously.

When I'm testing a script for production, I usually like to start with five or so concurrent execution loops and then pump it up in a stress environment, alone, to see how far I can take it. Sometimes I'll watch various performance counters during the test, but usually I'm either feeling lazy or don't think I'll gain much insight from the counters. Sometimes it is handy to watch Profiler's SP:StmtCompleted event to get a slightly more granular picture of the performance degredation, but if I'm testing a SELECT query I'll usually just watch how many times I'm able to execute it in a given amount of time. For an INSERT/UPDATE process, I may put some logging triggers in place (of course, I need to account for them in any performance figures I produce.

Usually I'll execute my test code in a simple loop, like:

DELCARE @EndDate DATETIME
SET @EndDate = DATEADD(mi, 10, GETDATE())

WHILE @EndDate > GETDATE()
    EXEC dbo.DoStuff

DoStuff is assumed to be some stored procedure that's being tested, or perhaps a stored procedure containing a bunch of other stored procedure calls and maybe some randomization or other logic to help make the tests slightly more realistic.

I like to run tests for 10 or 20 minutes at a time. And I like to have them automatically stop in case I get called away in the middle... One time I was running an INSERT load test on a shared development machine and forgot to turn it off before going home. The next morning was not fun.

Of course, none of this has any bearing on how to get these things to run synchronously! And that's really what I'd like to know from whoever's reading this post. My usual method is to copy my loop into the clipboard, then open a new instance of Query Analyzer and start hitting CTRL-N, CTRL-V, F5 as many times as I need to. Other methods I know about are the script that Ken Henderson included in The Guru's Guide To SQL Server Architecture and Internals and the Database Hammer utility included in the Microsoft SQL Server 2000 Resource Kit. I'm not a huge fan of Henderson's script -- it's just not graphical enough for my tastes -- and I haven't tried the Database Hammer yet.

So what's the best way to load test synchronous SQL? Do people prefer using just QA, or is the Database Hammer an amazing tool? I'm considering writing my own synchronous SQL execution tool for a book I'd like to write, and if such a tool would also be useful to readers of this blog it would certainly make sense for me to write it before I get to the book stage. I know I could use a better tool -- hitting CTRL-N so many times is giving me a minor case of carpal tunnel syndrome.

Any comments on exisiting tools and techniques or wishes for these kind of tools would be appreciated...