Have you ever wondered how you could take one or more .sql files you captured as a workload and run them against a SQL Server or Azure SQL Database to test performance or impact? Microsoft has a free tool to do just that – ostress.
Ostress.exe is part of the RML utilities. The first step is to download it from https://www.microsoft.com/en-us/download/details.aspx?id=4511. Along with the command-line tools, there is also a helpful help document!
Ostress allows you to specify one file, or a folder that contains multiple files, to run. You can also specify a number of connections to be made to the database, to simulate multiple users or applications running the same query. Each connection can then run the file one or more times.
The next thing you’ll need is one or more .sql files that the tool will run.
To run a load test, you’ll open RML cmd prompt and enter your command. A few common parameters, which you’ll be using:
-S – server name – this works for a SQL Server or an Azure SQL Database.
-E – Windows authentication. The other option is -U and -P for SQL authentication.
-d – database name.
-i – Path to batch file(s), such as C:\ostress\ostress_batch_file.sql.
-n – Number of connections to create.
-r – Number of iterations through the file each connection will make.
-q – Quiet mode – no result display.
-o – Output file directory.
A sample command which will take one file I created (ostress_batch_file.sql), create 5 connections, and run it twice on each:
ostress -SJesSb -E -dAdventureWorks2016 -iC:\ostress\ostress_batch_file.sql -n5 -r2 -oC:\ostress
In the cmd window:
The output folder contains one log file, and one output file for each connection.
One question I’ve been asked was how to build delays into the batch. My suggestion is to edit the T-SQL script, using WAITFOR DELAY, to accomplish that.
Give ostress a try when you want a simple load testing tool for SQL Server!