Parameter Substitution in SQLQueryStress for SQL Server Performance Tuning

By:   |   Updated: 2024-03-21   |   Comments (4)   |   Related: 1 | 2 | 3 | > Performance Tuning


You've likely seen the popular internet meme where some version of a refined person says, "I don't always test my code, but when I do, I do it in Production." The meme was funny the first few times I saw it, but it's not advice for thriving in the real world. A habit everyone can value is testing queries and stored procedures before releasing them out in the wild. Before moving SQL code to production, run it through SQLQueryStress using multiple parameters. But how do you pass in multiple parameters without building complex code?


In this article, I'll reveal how you can benefit from parameter substitution in SQLQueryStress and the steps to get started. We'll explore why dynamically passing parameters is preferred to using the same one repeatedly. If you're skipping parameter substitution when testing, you're missing out. Finally, we'll look at passing in a random date range, and I'll share a helpful query you can customize. By the end, you'll be ready to use parameter substitution on your next query before it goes to production.

What is SQLQueryStress?

If you're looking for a free, lightweight tool to test T-SQL queries, SQLQueryStress is a great choice. I mostly use it for its ability to simulate several sessions and iterations. It mimics multiple users running one or more queries at once, plus it has a slick GUI interface. Microsoft built a similar application called Ostress, and I recently watched Bob Ward use it in a demo. However, it lacks a user interface, which some people may prefer.

Adam Machanic created SQLQueryStress nearly 20 years ago, and Erik Ejlskov Jensen maintains the code in a GitHub repo. I wrote an article on getting started with SQLQueryStress. If you've never used it before, please take a moment to read it and come back once you've got it up and running.

Since you know what SQLQueryStress does, let's focus on one of its most powerful features.

Parameter Substitution Explored

Row Counts

Why would you want to use the parameter substitution feature in SQLQueryStress? Likely, your users run queries and stored procedures with multiple parameters. For example, if you have a query that accepts a start and end date parameter that limits the time between a specific period. The users may want to see data from a larger time frame than a single day or week. They may need to see an entire month, which means more data for SQL to process. On average, a query returning 10,000 rows will take longer than one returning 10.

Query Plan

The SQL Server optimizer builds and selects plans partly based on the cardinality estimates from the statistics on columns. If you have a column with few duplicates, it's said to have high cardinality. For example, imagine a table called Sales with 10 million rows. Say you have one person assigned as the Salesperson on most Sales records; that's low cardinality. You might get a different plan with that rock star salesperson versus one with only a few records. When testing the performance of a query, you want to use a range of different values.

Cache and Deadlocks

If you repeatedly use the same parameter value, you're reading pages already stored in cache. Parameter substitution helps by reading pages not in cache. Passing in a variety of values reflects a more real-world pattern. Also, passing in multiple parameters allows for detecting deadlock issues early when using SQLQueryStress for DML operations.

Now that we have reviewed why you want to use a variety of parameter values, let's see how we can do it in SQLQueryStress.

Building Our Dataset

Let's build a sample dataset highlighting the importance of using multiple parameters when testing query performance. The code below creates two tables. Our first table, Employees, only contains 10 records—we're a small but mighty company. The next table, Sales, contains one million rows. Two sales folks rarely sell anything; one's the CEO, which we expect since she's busy running the company. The other person lacks initiative and is on their way out.


USE [master];

    DROP DATABASE SQLQueryStressDemo;



USE SQLQueryStressDemo;

CREATE TABLE dbo.Employees
    EmployeeId INT NOT NULL,
    FirstName VARCHAR(25) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Title VARCHAR(50) NOT NULL,
    CONSTRAINT PK_Employees_EmployeeId
        PRIMARY KEY CLUSTERED (EmployeeId)

INSERT INTO dbo.Employees
(1, 'Karen', 'Reese', 'CEO'),
(2, 'Bob', 'Morgan', 'Sales representative'),
(3, 'Dione', 'Windsor', 'Sales representative'),
(4, 'Steve', 'Door', 'Sales representative'),
(5, 'Arun', 'Kumar', 'Sales representative'),
(6, 'Mikey', 'Jackson', 'Sales representative'),
(7, 'Jill', 'James', 'Sales Manager'),
(8, 'Steve', 'Hen', 'Sales representative'),
(9, 'Ishaan', 'Agarwal', 'Sales representative'),
(10, 'Kelly', 'Small', 'Sales representative');

    SalesId INT IDENTITY(1, 1) NOT NULL,
    EmployeeId INT NOT NULL,
    Amount DECIMAL(20, 2) NOT NULL,
        CONSTRAINT PK_Sales_SalesId
    CONSTRAINT FK_Employees_EmployeeId
        FOREIGN KEY (EmployeeId)
        REFERENCES dbo.Employees (EmployeeId)

WITH SalesData
           ABS(CHECKSUM(NEWID()) % 8) + 3 AS EmployeeId,
           ABS(CHECKSUM(NEWID()) % 100) + 1 AS Amount,
           DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + 365), '2023-01-01') AS CreatedDate
    FROM sys.syscolumns s1
        CROSS JOIN sys.syscolumns s2
    SELECT TOP 1000
           ABS(CHECKSUM(NEWID()) % 2) + 1 AS EmployeeId,
           ABS(CHECKSUM(NEWID()) % 100) + 1 AS Amount,
           DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + 365), '2020-01-01') AS CreatedDate
    FROM sys.syscolumns s1)
SELECT s.EmployeeId,
FROM SalesData s;

ON dbo.Sales (EmployeeId)
INCLUDE (Amount);

Test Query

The CEO asked us to create a query she can run throughout the day that focuses on sales for individual people. This request is super easy, and we start creating it. But before handing it over to the CEO, we want to test it. Remember, we test our code before releasing it to the end user.


SELECT s.Amount AS SalesAmount,
       CONCAT(e.LastName, ', ', e.FirstName) AS SalesPerson,
FROM dbo.Sales s
    INNER JOIN dbo.Employees e
        ON s.EmployeeId = e.EmployeeId
WHERE e.EmployeeId = 1;

Setting Up SQLQueryStress

Let's add this query in SQLQueryStress and set the iterations and the number of threads to 50, meaning we'll have 2,500 iterations. Once you've got that set, click GO and see what happens. Often, I'll click GO three or four times to get a rough idea of how long it takes.

Setting up SQLQueryStress

At first glance, the performance is awesome. Do you notice something about the value we're using in the filter predicate? The ID belongs to the CEO. If you remember from above, the CEO only has a few records in our sales table.

A more realistic sample would be any value minus the first two records (CEO and the slacker). A better way is to pass in all the records from our Employees table.

Parameter Substitution in Action

Before clicking Parameter Substitution, add the parameter to the query area as @EmployeeId, as seen in the screenshot below.

Parameter Substitution

Next, click Parameter Substitution to bring up the next window.

Parameter Substitution screen

We need to supply a query for the values we want to use. Even though the query for our example is simple, you can use something more complex. For example, suppose you wanted additional filter criteria for the employees' title or start date, or you can join other tables.


SELECT EmployeeId FROM dbo.Employees;

After clicking OK, let's execute our query again. You will notice two significant differences after clicking GO and give it a few more seconds.

SQLQueryStress Increased Reads and Time.

This test takes over 39 seconds, and SQL reads 4,700 pages versus the 1,500 from the first test. The results are neither bad nor good. With the new insights, we can make a more informed decision and move forward. Perhaps we need to limit the data further with a date filter. This simple test illustrates the importance of using multiple parameter values.

Random Date Ranges

Another area where parameter substitution shines is for date ranges. Suppose we have a query that reports on the sales for a given week. Like the example above, we could use a fixed date range from January 1 - January 5 and capture five working days. Is that a good representation? As in our previous example, the sales for this period might be low. A better solution would be to use variable time frames. Below is the code for adding the date filter.


SELECT s.Amount AS SalesAmount,
       CONCAT(e.LastName, ', ', e.FirstName) AS SalesPerson,
FROM dbo.Sales s
    INNER JOIN dbo.Employees e
        ON s.EmployeeId = e.EmployeeId
WHERE s.CreatedDate >= @startdate
      AND s.CreatedDate <= @enddate;
Parameter Substitution for a Date

After adding the code above to SQLQueryStress, click Parameter Substitution again and perform the next three steps.

Step 1

Add the code below to the parameter query area. This code returns a random start and end date within a range of 1 to 21 days. The start date is always before the end date.


;WITH startdate AS (
SELECT TOP 100 DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+365),'2023-01-01') AS StartDate
FROM sys.all_columns c1
SELECT StartDate, DATEADD(DAY,ABS(CHECKSUM(NEWID()) % 21) + 1,StartDate) AS EndDate
FROM startdate;

Step 2

Click Get Columns.

Step 3

Match up the columns with the correct parameters and then click OK.

When you click GO again on the main screen, something becomes apparent, and you might miss it using a static date range. The performance could be improved. We need to add the CreatedDate column to our existing nonclustered index or create a new one.


CREATE NONCLUSTERED INDEX [IX_Sales_CreatedDate_EmployeeId-Amount]
ON dbo.Sales (CreatedDate, EmployeeId)
INCLUDE (Amount);

I doubt it's surprising that adding the index above helps performance, but check out the differences below.

Index Before and After

Wrapping Up

Even if you take SQLQueryStress out of the equation, using multiple filter values when testing queries and stored procedures is ideal. For me, SQLQueryStress makes it easier. Imagine a developer building a new grid on a page that displays details about clients and allows bulk actions. The app works great until you pass in the thousands of rows that exist in production. When someone from the business brings this fact to light, the design needs to change. You can't always catch issues like these early on. But trying to shine a light on these facts by testing multiple values from the start can save hours of rework.

Key Points

  • No sensible person says testing your queries before releasing them to production is a terrible idea. It would be like someone having a problem with kittens or skittles. Yet, unless you test with several values, you're not testing.
  • Approach testing a query from the standpoint that you want to break it or at least uncover any flaws before you release it into production. Finding faults with the code we've labored over for days isn't fun, but what's even worse is when unsuspecting end users find it.
  • SQLQueryStress is a free tool that allows you to test queries on a larger scale with multiple parameter values. If you're not currently using it in your test plans, do yourself a favor and start today.
Next Steps
  • Would you like to learn more about ordering columns when creating an index based on their selectivity? I wrote the article, SQL Server Indexes to Improve Query Performance, looking at the topic in depth.
  • If you haven't already downloaded SQLQueryStress, what are you waiting for? It only takes a few minutes to get up and running. Please don't run this on a production server.
  • Do you need to generate random dates for a query? Check out my article, Generate Random Dates in T-SQL. Please feel free to take the scripts and make something better.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jared Westover Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-03-21

Comments For This Article

Monday, March 25, 2024 - 3:02:44 PM - Jared Westover Back To Top (92114)
@pds sha

Thank you for taking the time to read and comment.

Monday, March 25, 2024 - 3:01:57 PM - Jared Westover Back To Top (92113)
@Temidayo Thank you for the kind remarks. I'm glad it was helpful!

Friday, March 22, 2024 - 5:26:58 PM - pds sha Back To Top (92107)
Very nice article, which is very helpful and can save time before we wait till get something wrong!

Thursday, March 21, 2024 - 4:07:51 PM - Temidayo Back To Top (92103)
Lovely write up Jared.
Thanks for Sharing really enjoyed this one.

Been doing some personal studies on this particular and you nailed it.

get free sql tips
agree to terms