oStress Load Test: Run Many Sessions Against SQL Server

An oStress load test runs the same query from many connections at the same time. It is the quickest way to see how SQL Server behaves under a crowd, before real users arrive.

Why Test a Load Before It Arrives

Code that runs well on a developer laptop meets its first real test in production. That is when many sessions run it together. In my health checks, most clients arrive after the slowdown, and few have tested the load beforehand. A load test turns that surprise into a rehearsal. You pick a query, choose how many connections run it, and choose how many times each one repeats it.

oStress is the tool I use for this. It ships in RML Utilities, a free Microsoft package for SQL Server, and it needs no scripting. One command line starts the whole crowd.

Install RML Utilities and Find oStress

Search for RML Utilities for SQL Server, install the 64-bit package and accept the defaults. The installer puts ostress.exe in a folder named RMLUtils, inside the Microsoft Corporation folder under Program Files. Open a command prompt in that folder to run it. To run it from any folder, read Windows Path Variable: Run oStress From Any Folder.

The Switches That Matter

Run ostress with no arguments and it prints its help. These switches cover most tests.

Two switches set the size of a test. The -n switch is the number of connections that run at once. The -r switch is how many times each connection runs the query. Total executions equal -n times -r, so -n5 -r20 means 100 executions.

Run a Small First Test

The first oStress load test needs a table that records which connection inserted each row. The default for SessionId is @@SPID, the session ID of whoever runs the insert. Create the database on a test server.

IF DB_ID(N'OStressIntroDemo') IS NULL CREATE DATABASE OStressIntroDemo;
GO
USE OStressIntroDemo;
GO
DROP TABLE IF EXISTS dbo.GateLog;
CREATE TABLE dbo.GateLog (
    GateLogId int          IDENTITY(1,1) NOT NULL PRIMARY KEY,
    SessionId int          NOT NULL DEFAULT (@@SPID),
    Note      varchar(200) NOT NULL DEFAULT ('Visitor counted at the gate')
);

Now the oStress command. Five connections each insert 20 rows, and the -q switch keeps the console quiet. Replace the server name with yours.

ostress -S.\SQLDEV -E -d OStressIntroDemo -Q"INSERT dbo.GateLog DEFAULT VALUES;" -n5 -r20 -q

The -S switch takes a server name only. One reader found that a connection string with extra parameters after the server name made the connection fail. For more control, create an ODBC data source and point to it with -D.

No oStress? Use sqlcmd Workers

You can build the same crowd with sqlcmd, which ships with the SQL Server tools. Save the next file as worker.sql. The GO 20 line repeats the insert 20 times. The wait keeps each connection open for three seconds, so all five overlap and get their own session ID. Without it, early workers finish first and later ones reuse their IDs, and the check below shows fewer connections.

SET NOCOUNT ON;
INSERT dbo.GateLog DEFAULT VALUES;
GO 20
WAITFOR DELAY '00:00:03';

This PowerShell block starts five sqlcmd processes and waits for all of them. Run it in the folder that holds worker.sql.

$server = '.\SQLDEV'
1..5 | ForEach-Object {
    Start-Process sqlcmd -ArgumentList "-S $server -E -C -d OStressIntroDemo -i worker.sql -o NUL" -WindowStyle Hidden -PassThru
} | Wait-Process

Check the result by connection. Each worker should show 20 inserts, and the table should hold 100 rows.

SELECT ROW_NUMBER() OVER (ORDER BY SessionId) AS Connection, COUNT(*) AS Inserts
FROM dbo.GateLog
GROUP BY SessionId
ORDER BY Connection;

SELECT COUNT(*) AS TotalRows FROM dbo.GateLog;

The connection numbers here come from ROW_NUMBER, because session IDs change on every run. oStress adds a managed crowd from one command and an output folder for results and logs. It also has a replay mode for captured workloads, which neither sqlcmd nor SSMS offers. For a quick crowd, sqlcmd is enough. For a test you repeat every month, oStress is the better tool.

Raise the Numbers in Steps

Once the first check passes, grow the test one switch at a time. Move from -n5 to -n20, then to -n50. Keep -r large enough that the run lasts a few seconds. A run that ends in under a second tells you little. Starting the connections takes a share of that time.

Decide what you will read before you start. CPU, wait statistics and elapsed time answer different questions. Write down the numbers from the quiet server first, so you can compare the loaded run with them.

Keep the Test Safe

An oStress load test is real work for the server. Start small, such as -n5 -r20, and raise the numbers one step at a time. Run it on a test server, and not while others share that server. Prefer -E over -P, so no password lands in your history.

You could argue that a repeated insert says little about a real application. That is fair. One query can’t copy a mix of reports, updates and users. It does answer narrow questions well, such as what one hot table can take. The next posts in this series ask exactly that.

Read oStress vs SSMS: What Each Tool Is Built to Measure next. Then read sqlcmd vs SSMS: How to Time the Same Loop Fairly. After that comes Insert Workload Waits: WRITELOG and PAGELATCH_EX. The last one is Recovery Model Insert Test: FULL vs SIMPLE Under Load.

What to Remember

An oStress load test runs one query from many connections. Set the crowd with -n and the repeats with -r. Check the row count against -n times -r before you trust the result. Start small and grow the test in steps.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE OStressIntroDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE OStressIntroDemo;

A load test is not a prediction, it is a rehearsal you can repeat.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.