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
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 HammerDB (Formerly HammerOra)
HammerDB 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 HammerDB to generate and populate the schema for a database to load test against. HammerDB 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, HammerDB 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.
HammerDB on SQL Server: The Pros and Cons
HammerDB 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.
HammerDB 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 HammerDB, it isn’t directly comparable to published TPC-C benchmark results from major vendors. But it’s free, It’s quick and it’s easy.
Use Cases for Benchmarking
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 HammerDB by reading the “SQL Server OLTP Load Testing Guide” on the HammerDB documentation site.