Load Testing with SqlQueryStress

Do you have a new SQL server that you need to load test against? What about a new stored procedure that needs tested with various parameters? Maybe you’re just trying to punish your CPU? Whatever the reason, my favorite tool for these scenarios is Adam Machanic’s SqlQueryStress. Before we run through some examples, check out SqlQueryStress on GitHub or get SqlQueryStress from the Microsoft Store.

Configuring SqlQueryStress

Once installed, opening SqlQueryStress will present you with the window below:

To start our test, we’ll want to choose where to run our script. We can click the Database button and choose the server and database to test against:

We’ll test against StackOverflow2013 on CALLIHAN. Once we set our server and database we can click Test Connection to test our connection and assuming no issues (you know what happens when you assume) we’re ready to click OK and configure a few more settings.

In this example, let’s run a stored procedure to get the top posts for a particular user. If we want to run the exact same query over and over again we can run the following:

Running the same query repeatedly using a @UserId value of 1 may not be close to a realistic scenario. In reality, there are probably going to be different parameters being passed. This leads us to the Parameter Substitution button. Let’s remove the “= 1” from our query and click the Parameter Substitution button.

We’ll set the Parameter Query to grab the Id from Users. Then, we’ll click Get Columns and we’ll see Parameter Mappings show at the bottom. We’re using one parameter but multiple can be used if desired. We’ll click the Column drop down menu so that we see Id and click the Datatype field to confirm our type:

After we click OK this query will now be used to gather @UserId values from Users to run with the usp_GetTopPosts stored procedure.

Two Main Options

Two options we’ll want to set next are Number of Iterations and Number of Threads. Let’s start with threads. The amount of threads will represent how many sessions run our query. We can then specify how many times the query runs per thread by setting our iteration value. For example, let’s set threads to 4 and iterations to 10 which means each of the 4 threads will run the query 10 times for a total of 40 iterations.

Go Go SqlQueryStress

If we click GO the queries will begin running and we’ll see our results:

We have confirmation of our 40 total iterations completed along with the elapsed time. We also have our averages for Client Seconds/Iteration, CPU Seconds/Iterations, Logical Reads/Iteration, and Actual Seconds/Iteration.

Total Exceptions reads as 0 but if we did have errors we could select the “…” button next to the count to see more information. In this case, if I change the parameter name to something invalid and try running again, we’ll get errors where selecting the “…” button will show:

If you want to see exactly what queries were running, you can begin an Extended Events session before running SqlQueryStress and see the queries there to confirm:

Even if you don’t have a use for it today, go download SqlQueryStress and play around with it now. It’ll come in handy when you need it.

Thanks for reading!