Load testing – real, serious load testing – is hard.
In a perfect world, you want to exactly simulate the kinds of queries that the end users will be throwing at your SQL Server. However, in the words of a timeless philosopher, ain’t nobody got time for that.
Instead, let’s use the neato open source tool SqlQueryStress (latest exe) to fake it. This is an oldie but goldie app that will run any one query thousands of times (or more) from dozens of sessions (or more), all from the comfort of your desktop:
After you install it on your desktop (or a VM in the data center, whatever, just not the SQL Server you’re trying to load test), click the Database button to set up your connection string. In this instance, I’m pointing it at one of my Availability Groups, using Windows authentication. As soon as I set the server and auth methods, the database list gets populated so I can set my default database:
Then it’s time to pick the query to run.
But you want to test more than one query at a time, right? You want to test a variety of different queries running all at once.
Rather than calling a single query, call a “shell” stored procedure that runs other queries. Here’s how it works:
- Declare an integer, and set it to a random number
- Based on the mod of that number, run a stored procedure
(for example, if it’s divisible by 3, run sp_C,
else if it’s divisible by 2, run sp_B,
else run sp_A.)
Since SQLQueryStress will be calling this stored proc dozens of times at once, you’ll end up with a variety of different queries running simultaneously.
Let’s get a little more complex. Here’s what mine looks like for one of my query tuning demos:
CREATE PROCEDURE [dbo].[usp_RandomQ] WITH RECOMPILE AS SET NOCOUNT ON DECLARE @Id INT SELECT @Id = CAST(RAND() * 10000000 AS INT) IF @Id % 7 = 0 EXEC dbo.Refresh_ReportByVoteType ELSE IF @Id % 6 = 0 EXEC dbo.Refresh_ReportByBadge ELSE IF @Id % 5 = 0 EXEC dbo.GetBadgesDetails @Id ELSE IF @Id % 4 = 0 EXEC dbo.GetCommentsDetails @Id ELSE IF @Id % 3 = 0 EXEC dbo.GetPostsDetails @Id ELSE IF @Id % 2 = 0 AND @@SPID % 2 = 0 EXEC dbo.GetUsersDetails @Id ELSE EXEC dbo.GetVotesDetails @Id GO
WITH RECOMPILE – I use this because I don’t want the usp_RandomQ stored procedure to show up in my execution plan stats. The work involved with building this execution plan isn’t significant, and it won’t be the largest part of my workload. (Oh, I wish it were.) All of the stored procs it calls will still show up in the plan cache, though.
@Id parameter – note that some of the stored procs take an @Id. For example, the stored proc GetBadgesDetails takes @Id, and uses that to look up a particular badge number’s details. This is handy because each of my stored procedures don’t have random number generators – they’re designed to mimic more real-world stored procs that have input values. If you wanted to get really fancy and test procs with lots of parameters, you’ll need to generate those in usp_RandomQ. You don’t want to hard-code the same values because then that relevant table data will end up getting cached in memory.
@@SPID – some of my workload queries simulate blocking. Due to the wonders of random number generation and very fast queries, if a blocking chain starts on any two sessions, then eventually the rest of the sessions will also call the stored proc that’s susceptible to blocking. After a few seconds, the only symptom my server will have is blocking – and that’s no fun. Instead, by using @@SPID %2 before calling GetVotesDetails (which gets blocked in my scenario), I make sure that no more than half of my sessions will get blocked at once.
The end result is beautiful – well, at least if you want something that looks like a production server getting hammered with all kinds of different queries:
I love using this quick-load-generation technique in our performance tuning classes. It’s a great way to show a server that looks like home, and gets students to figure out which queries are causing problems – and which ones are just harmless background noise.