So you wanna run a load test against SQL Server.
One thing I need you to understand first: you have to provide the database and the queries. Almost all of the tools in this post, except the last one, are designed to help you run queries, but they don’t include the queries. The whole idea with load testing is that you’re trying to mimic your own workloads. If you’re just trying to test a server with generic workloads, start with my post, “How to Check Performance on a New SQL Server.”
Single-Query Load Tests
These tools let you write a query, and then run that query lots of times to stress load. Of course, this doesn’t really mimic production workloads, which will run lots of different queries concurrently. You can kinda mimic that with my technique to call random queries from a stored procedure, but even with that, we’re talking about a pretty low-tech technique here.
Microsoft RML Utilities and OStress (download – instructions) – very barebones command line tool that lets you run a query across a number of sessions, for a number of threads. No graphical user interface here – it’s very stripped-down and non-intuitive, but that’s also kind of the draw: it’s fast and simple.
(Open source) SQLQueryStress (download – instructions) – graphical utility originally written by Adam Machanic, and now maintained by Erik Ejlskov Jensen. I find this way easier to use, but it’s also slower than OStress. When you have a really well-tuned workload that returns hundreds or thousands of rows per query, it’s not unusual to hit ASYNC_NETWORK_IO waits with SQLQueryStress when it’s unable to keep up with digesting the results, something I don’t see as often with OStress.
Load Replay Tests
These tools capture a workload (either from production, or from a group of users clicking around in dev/QA), and then help you replay that same workload again and again, either on the same SQL Server or different servers.
(Open source) WorkloadTools (download and instructions) – Gianluca Sartori’s command line tools to collect a workload (either with tracing or XE), analyze the data, and replay the workload in real time. If I was a full time DBA in charge of SQL Server load testing at a company, this would be the one I’d start with.
Microsoft Profiler – (built in) – in theory, you can use Profiler to capture a trace of queries in production, and then replay that trace against another server. In practice, it doesn’t work well because it doesn’t accurately mimic the concurrency issues you hit in production. I don’t recommend this as a load testing solution.
Microsoft Distributed Replay (instructions) – this tool aimed to solve Profiler’s weaknesses by capturing a Profiler-style trace, but then replaying that trace in a coordinated fashion across multiple load test servers, all aimed at the same SQL Server. Before you go too far down this road, watch Gail Shaw’s Pluralsight course on it, and read the tales of woe in the Microsoft forums. The learning curve on this is pretty steep – think days and weeks of setup & experimentation time to get this working really well, not hours.
Microsoft Database Experimentation Assistant (download – instructions) – this builds atop Distributed Replay to let you run A/B tests with different server settings, indexes, even SQL Server versions, and then tells you which queries got better (and which ones got worse.) The drawback here is that you gotta get Distributed Replay working before you can progress to this point.
(Open source) HammerDB (download – instructions) – can be used either to run a standard synthetic workload (like a TPC benchmark), or your own custom queries. This one can test not only SQL Server, but also MySQL, Oracle, PostgreSQL, and more. I would only go down this road if I was a performance tuning specialist in a large company with lots of databases – this thing is really specialized.
Regardless of which tool you choose, remember to use the tips from my post, “How to Load Test a Database Application.”