Recently I was looking to generate a quick workload against an instance. I wanted to add to the amount of work my system was doing by executing a number of queries simultaneously. There are a variety of ways to do this, but I just wanted a quick set of queries to run against a SQL Server instance and produce some load. In this piece, we'll look at a few ways of generate load against your servers, simulating a number of users.

There are software packages that do this, but many of the commercial ones are quite expensive. The commercial tools have worked well for me in the past, but they are really geared towards generating a large, repeatable load for application testing. If you are after something simpler, this post might help you.

Note that I'm looking to generate a fairly predictable load, not a random one, or one that simulates the actual queries run by users. SQL Server includes the Distributed Replay feature that can use an actual trace from an existing instance and replay this against a different instance, simulating multiple clients. There's also a random workload generator from Jonathan Kehayias at SQLskills that you might modify for your uses.

I want to show you a simpler method that might help with experimenation and lab environments.

The Cumbersome Way

The first example of this is easy. I opened a query in SSMS. You can see this below, and note that I’ve added a value to the GO statement. This means this batch will execute 50 times from SSMS.

That’s a load, but it’s a single threaded load.  I could add a small delay and then copy this query to another window.

Now I have two windows, and I can click execute in one, then click to the other, and click execute again. That’s easy enough, but cumbersome. I need to click in each window and then click Execute. There's a bit of dexterity involved and having to do this quite a few times in an experimental fashion gets annoying quickly.

Taking Advantage of CMD

Those of you that might have spent part of your career working with command lines might know this trick. If you open a command line and type SQLCMD (with parameters), what happens? You get this:

The SQLCMD process is waiting for a command. SQLCMD has taken over my command prompt, which is fine. That's usually what I want. However, what if I do this?

In this case, the Start command has spawned a new window, passing the program to the new window. I can both type in my original command prompt, as well as use SQLCMD in its own window. In fact, I can spawn multiple SQLCMD windows. You can see two new windows below, each running a SQLCMD instance.

This can be useful for me in building a load generator. First, let's put a query in it's own .sql file. This makes changing the query simple, and also makes a batch file simpler.

Now that I have my query, I add a .CMD file in the same folder, with mutliple lines. Each of these lines has a SQLCMD call, with a START call before it. This allows me to spawn a series of threads, each of which will be executing the script above against my SQL Server.

I can add more queries or more threads by altering this file to include different SQLCMD queries or having more copies executing all at once.

SQL Load Generator

There's one more easy way to do this that I'll show, though this isn't using just built in tools. There is a SQL Load Generator on Codeplex. This is a fairly simple C# program that builds a GUI and can execute multiple queries against a server. You need to install it, but once you do, you can quickly generate some load. Here's the basic view.

There are settings to set default servers, queries, etc. I don't want to describe how to use this tool, but it's fairly intuitive. I can add a query to run against my server.

Note that I specify the query inline, add connection information and number of concurrent queries, and then can click start. I can add more queries as well.

When I run these, I'll get a load against my instance.

While this works well, it feels like an application I'd write. There isn't a lot of error handling and I broke this quite a few times, but it does allow me to generate a load against the server and track what's executed (or failed) over time.

Building a Load

Ultimately, all of these tools tend to just repeat a set of queries over and over. While I could have a series of batches inside each query, with some delays, I'm still modeling a load and not necessarily repeating it. However these loads do allow you to run some tests and see how hard your server might be working to handle a series of queries. In my case, I wanted to see if a deployment that changed from a scalar function in a query to a CROSS APPLY would visibly affect CPU. It did and it was cool.

There are other tools as well to generate different types of loads, and I'd encourage you to experiment and work with some of them to develop some familiarity and skill that will make you comfortable with the tools. With that comfort will come the likelihood that you'll actually use some tool to generate test loads in the future, which might help you better architect an application, size hardware, or just ensure your system is operating as you expect.