I’ve discovered that DBAs use different methods to accomplish what amounts to the same thing: Generating concurrent activity on a server. I want to explore a number of methods I’ve seen. For each method, I want to call one particular procedure, many times at once, and often… Oh, and for free.

Why are we doing this?

For Testing: The whole point is to generate concurrent activity on the server. Testing with this kind of activity is one of the only ways to avoid resource contention issues.

For Demos: Concurrency issues are so common and so varied, that it’s not surprising how often we see demos that depend on this kind of generated database activity.

For Troubleshooting: This is my favorite reason. A concurrency issue can’t be fixed well unless it can be reproduced reliably on some test environment. That’s why the methods below have a spot on my troubleshooting toolbelt.

The whole idea is to get many workers active on SQL Server at once.

For each method below, look out for the place where I specify these “parameters”

Number of virtual workers (50 in my example)

Number of iterations (for instance 10,000)

Connection string

Procedure to execute (s_DoSomething in my case)

One other thing to watch for is the overhead that this tool puts on the machine. Ideally, the method is suitable to run this from the same test environment that the SQL Server sits on. So I want my method to be fairly lightweight. This means that it’s best to handle iterations on SQL Server which cuts down on overhead associated with opening connections. So in most cases, instead of

Notepad + DOS Method

I adapted this method from a clever trick I saw once. It was Paul Randal giving a demo on tempdb contention. You can find a video of that demo by visiting this newsletter.
It’s a simple idea. You have two batch files, the first is called Run.bat:

Except that I don’t think I got it quite right. Whatever I changed makes this method unsuitable.

The script schedules a job 50 times, but it takes about a minute just to schedule them all. Once they’re scheduled, the jobs take time to start and not enough of them work in parallel to generate the needed concurrent activity so I give this method a “skip”. If you’re really comfortable with powershell, maybe you can get this to work faster. If you can, let me know.

C#

This is my preferred method. It started out as the program I used to test upsert concurrency at http://michaeljswart.com/go/upsert but a friend at work showed me that .net 4.5 has some nifty new asynchronous methods that make it look nicer, perform faster and weigh lighter.

HammerDB

HammerDB (originally HammerOra) is a free utility that allows users to run benchmarks against various environments. Although it was originally built for running benchmarks on Oracle, the utility now works on Windows and for SQL Server (hence the name change). I was first introduced to the utility via Kendra Little (again):

Follow these links to learn how to use the tool for its typical function, running benchmarks. Then, once you know how to do that, it’s a quick step to repurpose the tool for your own concurrent activity. For example, replace HammerDB’s generated script with this one:

It’s easy to fire off a handful of sessions, but how do you easily fire off 50 powershell sessions? or 500? When I evaluated powershell, I found that each session takes enough computer resources (memory/cpu) that I couldn’t be sure that what I was measuring wasn’t caused by the powershell framework itself.

I think HammerDb and Jonathan’s solutions are good for benchmarks. Like when you’re testing and monitoring SQL Server but you’re agnostic about the app.

For my own goals, I want to test and debug a particular application’s concurrency (that means lots of concurrent threads. It’s those goals that I used as the basis for my rating.

Michael, great post! A few years ago I blogged about how I generated workloads remotely with PowerShell, which – if you have more than one machine available – eliminates the problem PowerShell has of becoming such a major part of the observer overhead when running locally:

Now, I am the last guy you’ll see saying “Use PowerShell” in cases where another tool is more appropriate. But in this case I find it a bit easier to generate a bunch of jobs in PowerShell than to create a C# app that – with my skills – will likely take over the box. 🙂

Good point Aaron,
I think what you and I find easier are different. And that’s fine. I find C# an extremely handy tool.

My actual plan is to do this activity once. So when this tool is complete, I can click it once and it will launch several clients which call a server’s tempdb.dbo.s_DoSomething. Then s_DoSomething is where I put all the test functionality. s_DoSomething is next week’s post and I have a feeling it will equally as useful whether you like my prefered methods here or you stick with powershell. Stay tuned.

On another note, I was surprised that your post was part of a TSQL Tuesday (#15) on the topic of automation. That’s what last month’s topic was as well. I think we have our first duplicate. See the whole list

I don’t want to claim that PowerShell is the best option here but, one way to make your PowerShell snippet much faster is to call sqlcmd instead of loading the SQLPS module. Loading the SQLPS module is very slow and, when you said that it took a minute to create 50 jobs, I was surprised. I never experienced that type of lag with the Start-Job Cmdlet. However, I can see loading SQLPS taking that long. Just my two cents…