By: Jared Westover | Updated: 2024-03-21 | Comments (4) | Related: 1 | 2 | 3 | > Performance Tuning
Problem
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?
Solution
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.
-- https://www.mssqltips.com
USE [master];
GO
IF DATABASEPROPERTYEX('SQLQueryStressDemo', 'Version') IS NOT NULL
BEGIN
ALTER DATABASE SQLQueryStressDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SQLQueryStressDemo;
END;
GO
CREATE DATABASE SQLQueryStressDemo
ALTER DATABASE SQLQueryStressDemo SET RECOVERY SIMPLE;
GO
USE SQLQueryStressDemo;
GO
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)
);
GO
INSERT INTO dbo.Employees
(
EmployeeId,
FirstName,
LastName,
Title
)
VALUES
(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');
CREATE TABLE dbo.Sales
(
SalesId INT IDENTITY(1, 1) NOT NULL,
EmployeeId INT NOT NULL,
Amount DECIMAL(20, 2) NOT NULL,
CreatedDate DATETIME NOT NULL
CONSTRAINT PK_Sales_SalesId
PRIMARY KEY CLUSTERED (SalesId),
CONSTRAINT FK_Employees_EmployeeId
FOREIGN KEY (EmployeeId)
REFERENCES dbo.Employees (EmployeeId)
);
GO
WITH SalesData
AS (SELECT TOP 999000
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
UNION ALL
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)
INSERT INTO dbo.Sales
(
EmployeeId,
Amount,
CreatedDate
)
SELECT s.EmployeeId,
s.Amount,
s.CreatedDate
FROM SalesData s;
GO
CREATE NONCLUSTERED INDEX [IX_Sales_EmployeeId-Amount]
ON dbo.Sales (EmployeeId)
INCLUDE (Amount);
GO
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.
-- https://www.mssqltips.com
SELECT s.Amount AS SalesAmount,
CONCAT(e.LastName, ', ', e.FirstName) AS SalesPerson,
s.CreatedDate
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.
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.
Next, click Parameter Substitution to bring up the next window.
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.
-- https://www.mssqltips.com 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.
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.
-- https://www.mssqltips.com
SELECT s.Amount AS SalesAmount,
CONCAT(e.LastName, ', ', e.FirstName) AS SalesPerson,
s.CreatedDate
FROM dbo.Sales s
INNER JOIN dbo.Employees e
ON s.EmployeeId = e.EmployeeId
WHERE s.CreatedDate >= @startdate
AND s.CreatedDate <= @enddate;
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.
-- https://www.mssqltips.com ;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.
-- https://www.mssqltips.com CREATE NONCLUSTERED INDEX [IX_Sales_CreatedDate_EmployeeId-Amount] ON dbo.Sales (CreatedDate, EmployeeId) INCLUDE (Amount); GO
I doubt it's surprising that adding the index above helps performance, but check out the differences below.
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.
About the author
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 MSSQLTips.com, 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
