Stress Testing

for RuBoard

There are a number of powerful third-party tools available for stress testing SQL Server. Many of these are quite expensive. What I'm about to show isn't as advanced as these types of tools, nor does it have the extensive feature set that many of them have. But it does have one advantage over them: It's free. STRESS.CMD, my SQL Server stressing tool, is a poor man's robo-testyou give it a T-SQL script to run and it runs the script a specified number of times against a SQL Server. STRESS.CMD has five main features:

1. It can run a specified T-SQL script repetitively against a SQL Server.

2. It can run one script multiple times or run many scripts once, or some combination of the two.

3. It can route output to the console or it can log each script's output individually.

4. It can display, hide, or minimize each script window.

5. It can run all scripts concurrently or it can run them one after the other.

NOTE

Microsoft publishes a tool called SQL70IOStress that tests the performance of a disk subsystem for use with SQL Server. This is not the kind of stress test I'm talking about here. What I'm referring to here is a tool that's capable of simulating multiple users connected to a SQL Server simultaneously , each running Transact-SQL queries against the server that stress it in some way.

Listing 18-4 shows the full source code to STRESS.CMD. This is a routine I originally wrote many years ago (on OS/2, no less) and have gradually developed over the years . You'll need the Windows 2000 command extensions enabled (they're on by default in Windows 2000) to use this code:

Listing 18-4 STRESS.CMD, a homegrown SQL Server stress test tool.

 @echo off REM Check for too few or too many parms and error IF (%1)==() GOTO ERROR IF NOT (%9)==() GOTO ERROR REM Set some default values SET ggmask=%1 SET ggtimes=1 SET ggwait=NO SET ggserv=(local) SET gguser=-E SET ggwaitparm=/NORMAL SET ggwin=NORMAL SET ggout=YES REM Move parameters to variables IF NOT (%2)==() SET ggtimes=%2 IF NOT (%3)==() SET ggwait=%3 IF NOT (%4)==() SET ggwin=%4 IF NOT (%5)==() SET ggout=%5 IF NOT (%6)==() SET ggserv=%6 IF NOT (%7)==() SET gguser=%7 REM Set this to empty if not supplied SET ggpwd=%8 REM Set this to empty SET ggoutparm=%9 REM Further process some of the parameters IF NOT (%ggpwd%)==() SET ggpwd=-P%ggpwd% IF /i %ggwait%==YES SET ggwaitparm=/WAIT IF NOT %gguser%==-E SET gguser=-U%gguser% IF /i %ggout%==YES SET ggoutparm=-o%%~nf.OUT REM Run the script(s) using a nested loop REM REM The inner loop iterates through the files matching the mask and runs them REM The outer loop executes the inner loop the number of times specified FOR /L %%i in (1,1,%ggtimes%) DO FOR %%f IN (%ggmask%) DO START "%%f"       %ggwaitparm% /%ggwin% OSQL.EXE -S%ggserv% %gguser% %ggpwd% -i%%f       %ggoutparm% GOTO NOERROR :ERROR echo You must specify a script to run SET ERRORLEVEL=1 ECHO STRESS.CMD ECHO . ECHO Runs specified Transact-SQL script(s) multiple times simultaneously ECHO . ECHO Copyright (c) 1992, Ken Henderson. All rights reserved. ECHO Based on my code in User-to-User, PC Magazine, March 26, 1991. ECHO . ECHO USAGE: STRESS script [N] [wait] [windowstyle] [saveoutput] [server] [user] [password] ECHO . ECHO where: ECHO   script = the script or mask you want to run ECHO   N = the number of times you want to run it (default 1) ECHO   wait = YES to wait for each script to finish before running next (default NO) ECHO   windowstyle = type of window to create for each script execution: ECHO . ECHO          MIN = minimized MAX = maximized B = no window NORMAL = normal window ECHO . ECHO   saveoutput = YES to save output to file using -o OSQL parameter (default YES) ECHO   server = the server to run it on (default (local) ) ECHO   user = your SS user name (default - use trusted connection) ECHO   password = your SS password (default - use trusted connection) :NOERROR 

STRESS.CMD takes up to eight parameters. These parameters are positional (in other words, to specify parameter five, you must also specify parameters one through four) and have the meanings detailed in Table 18-2:

Table 18-2. STRESS.CMD Command-line Parameters

Parameter	Meaning
SCRIPT	The script or mask you want to run
N	The number of times you want to run it (default, 1)
WAIT	YES to wait for each script to finish before running the next one (default, NO)
WINDOWSTYLE	Type of window to create for each script execution: MIN, minimized; MAX, maximized; B, no window; NORMAL, normal window (default, normal)
SAVEOUTPUT	YES to save output to file using OSQL -o parameter (default, YES)
SERVER	The server on which to run it (default, ( local ))
USER	Your SS user name (default, use trusted connection)
PASSWORD	Your SS password (default, use trusted connection)

A call to STRESS.CMD might look like this:

 stress stress.sql 10 no normal no dragonzlair 

In this example, the script stress.sql will be executed ten times. We won't wait for each execution to finish before starting another, nor are we interested in saving the output to a file. The name of the server we're connecting to is dragonzlair, and we'll use a trusted connection to log in.

Another STRESS.CMD might look like this:

 stress stress*.sql 100 no min yes dragonzlair monty python 

Here we're going to execute all the scripts that match the mask stress*.sql in the current directory. Second, we're going to execute any scripts we find 100 times asynchronously. Third, we're going to minimize each script window and save the output of each execution to a file (using OSQL's -o option). Last, we'll connect to SQL Server dragonzlair using user name monty and password python.

As you can see, you can come up some pretty exotic combinations to throw at your servers. Anything you can put in a T-SQL script, you can run via STRESS.CMD. You can run multiple scripts or just one, and you can run them as many times as you want.

You can run scripts concurrently or synchronously and can direct their results to the screen or to text files. You can cause Attention events on the server by pressing Ctrl-C to stop a running script, and you can stop the whole process by pressing Ctrl-C repeatedly until you're asked whether to terminate the command batch.

STRESS.CMD takes much of the tedium out of stress testing SQL Server. And it does so with a very small .CMD file that you can customize or use in conjunction with other operating system commands.

Some years ago I wrote a tool like this in C. It used the DB-Library API to do much of what this command file does. It was a handy tool, but the problem was that it seemed I was never done with it. People kept asking me to add "just one more feature" to it. I'd find myself still up at five o'clock in the morning working on "just one more feature" just to keep my users happy. Finally, I decided to scrap it and publish something people could change themselves if they wanted to. So I blew the dust off something I'd written for a magazine, changed it a bit, and STRESS.CMD was born. These days, when someone wants "just one more feature," I say, "Hey, you've got the sourcehave at it!" Now, if I could only find a way to get them to work for free on the rest of my code.

for RuBoard