Do you have a SQL Server that’s not getting enough action?
Maybe you have a new piece of production or lab hardware that you’d like to benchmark, to see how it compares with other gear in your environment.
Or maybe you just want to run some activity against your own test SQL Server instance to practice working with an active database. This can be incredibly useful at any stage of your career as a SQL Server DBA or Developer— sometimes you want to simulate some database load to test your troubleshooting scripts, or to demonstrate or learn about SQL Server features.
Writing Custom Load Testing is Hard Work
I’m training for a half marathon. Why shouldn’t my SQL Server instance work out with me?
To simulate load against a database, you want to have multiple application users and a mix of read queries and data modification statements. You don’t want to always update a single row to the same value, or just insert the same dummy value over and over again, though.
It’s certainly not impossible to write your own load generation scripts in Powershell, C#, or other languages, but it’s time consuming, and you need to create (or restore) a database schema and data set to work with the test.
The Free and Easy Way to Stress Your SQL Server: Simulate OLTP Database Load with Hammerora
Hammerora is a free, open source tool that allows you to run TPC-C and TPC-H benchmarks against SQL Server, Oracle, MySQL, and PostgreSQL. You can use Hammerora to generate and populate the schema for a database to load test against. Hammerora also allows you to configure the length of a test run, define a warm up period, and set the number of virtual users for each run.
Best of all, Hammerora has an autopilot sequence that lets you stitch together a sequence of multiple runs at varying levels of virtual users — you can use this to easily get a curve of results to see at what level of virtual users performance levels off. You can also use it to simulate a varying workload for demonstration or research purposes.
Hammerora on SQL Server: The Pros and Cons
Hammerora is a free tool, so it’s an extremely accessible, quick way to start benchmarking or simulating load. The autopilot feature also makes running workloads fairly automatic.
The primary downside is that there’s a bit of a learning curve. The user interface isn’t extremely intuitive and can take a bit of getting used to. After you use the tool for a few times it is much easier.
Hammerora also doesn’t run every benchmark. It does not run the TPC-E benchmark, for example, which is a more recently developed OLTP benchmark that SQL Server loves to tune for. You should understand that if you run a TPC-C benchmark with Hammerora, it isn’t directly comparable to published TPC-C benchmark results from major vendors. But, don’t forget, it’s free, It’s quick, it’s easy, and it’s pretty awesome!
Use Cases for Benchmarking
Benchmarking: It lets you make line graphs!
Benchmarking workloads won’t mimic the exact traits of your applications. Every workload is unique and will have different bottlenecks on different systems. Benchmarking with a defined workload is still very powerful for many use cases, including comparing performance between:
- Multiple environments (example: old physical server, new virtualized environment)
- Different points in time with variable factors (example: performance on a VM using shared storage and shared host resources)
- The point before and after a configuration change
Ladies and Gentlemen, Start Your Benchmarks
Get started with Hammerora by reading the “SQL Server OLTP Load Testing Guide” on the Hammerora documentation site.
Rowland June 5, 2012 | 1:29 pm
Looks like a great way to get people to actually benchmark performance.
I seem to recall that Microsoft strictly prohibits benchmarking SQL Server and publishing the results. I believe I’ve read that in the licensing blah-blah-blah. Just a word of caution so the suits don’t come after people.
Kendra Little June 5, 2012 | 1:40 pm
Great comment. It turns out there’s a name for this — it’s the “DeWitt clause,” and the story is that it originated after some benchmarks published by David DeWitt caused a flurry in some circles. It looks like it’s in the licenses for all the major database vendors.
The reasons behind this clause are all about benchmark comparisons. The vendors don’t want a non-approved benchmark run on a system they consider to be configured incorrectly to reflect badly on them.
I’m guessing, for example, that my MacBook Pro’s VM wouldn’t be considered to be “configured correctly” for official benchmarking.
Hammerora documentation does cite that it doesn’t implement the TPC benchmarks in exactly the same way they’re “officially” run. So I don’t think that you could claim that you’ve truly officially benchmarked either way.
I think if someone were to collect data and write a blog post that published the data as performance comparisons between A and B, that would be OK, as long as they weren’t calling it an official benchmark that compares to the real TPC-C benchmark. But, of course, it’s important to know the rules so you make an informed decision about what you want to publish or not!
Thanks very much for your comment– interesting stuff.
Anthony Sammartino June 8, 2012 | 1:49 pm
Great post Kendra. Thanks. I was searching all over the web this morning for a tool to performance test SQL Server.
Uday Vallamsetty December 6, 2012 | 1:53 pm
Thanks Kendra .. Hammerora seems to be very useful tool for testing the different DBMS’. Having an open source tool than can load/test both Oracle and MSSQL is very useful. I am planning to use this to benchmark our Database Virtualization server ..
Do you know if anything like this exists for TPC-E ?
Kendra Little December 6, 2012 | 3:06 pm
I don’t know of any free tools that do TPC-E. Quest’s Benchmark Factory does it for SQL Server (and possibly other platforms too).
Wes Crockett December 17, 2012 | 5:39 pm
Kendra,
Thank you for this post! I have a question though, and forgive me if I am being noob-ish.:
With the release of SQL Server Distributed Replay, what is the benefit of using HammerDB (Formerly Hammerora)? Are these things meant to measure the same things?
We will be implementing our system for a very large govt. agency with a pretty impressive server topology, and I want to be able to measure how our system holds up to that scale (this will be bringing our application up a few orders of magnitude in terms of active/concurrent users.)
Thank you,
Wes
Kendra Little December 17, 2012 | 5:49 pm
Hi Wes,
Replay technologies are great, but they don’t cover all the basis if you have a read-write workload. Let’s say my recorded workload is updating and deleting values— if I replay it and want to increase the volume of the workload, I may end up trying to redo work that’s already been done and doesn’t “magnify” properly. (Deleting rows that are already gone, update statements that now find 0 rows to update, etc). If I try running the same insert statement more than once, I may have issues with primary key violations.
Test workloads that you can more reasonably scale up and down can help better to things like this. The TPC-C workload is quite limited and isn’t the ideal one. Ideally you the application in question has its own test workload that’s been designed to simulate it which is scalable. But since that’s a lot of work, I think there will be room for using TPC-C, TPC-E, and TPC-H workloads to test performance under different levels of stress for many years to come.
Thanks for the comment!
Kendra
Wes Crockett December 17, 2012 | 9:39 pm
Thanks for this reply, Kendra! I go into a meeting tomorrow with our director of development and a few other people to discuss this. I will make the recommendation that we use a product like the one outlined here.
Do you know, off the top of your head, does Red Gate have a tool for this?
Lonny Niederstadt February 20, 2013 | 7:00 am
Excellent! I had only seen SQL Server tcp-c, and no mention of tpc-h, in the hammerora/hammerdb documentation previously. And not too long ago it was noted on other blogs that TPC-H was available in hammerora for Oracle But after installing hammerdb 2.11, the tpc-h load option for SQL Server is easy to find.
Pingback: An Introduction to SQL Server IO for Developers (video) - SQL Server Blog - SQL Server - Telligent