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.
53 Comments. Leave new
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.
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.
This is great article, i am trying to get some more info on Tpc-h type. The documentation on Hammerdb’s site doesn’t exist any more, by chance would you have any details on that?
Try this link: http://www.hammerdb.com/document.html
And thank you for your comment, I just went back into the blog post and updated the name of the product to HammerDB and updated the links I found to their new site. Should help out others who come across this, I hope.
Great post Kendra. Thanks. I was searching all over the web this morning for a tool to performance test SQL Server.
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 ?
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).
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
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
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?
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.
Thanks Kendra. I read the docs and understand how to determine the optimal number of warehouses, but I couldn’t determine optimal number of users per warehouses. Any suggestions?
The number of users is how many “workers” you have. If you’re setting this when you’re populating the warehouse, it’s just how many threads are going to be used to do the population. Base it on how many CPUs you have in the environment and how much power you think you can give it.
When you’re running tests, the number of virtual users is how many active workers are going to do the test. I like to use the autopilot feature to program a run with a series of different amounts of virtual users, and graph the results.
Hope this helps!
First of all, what a great youtube video it was lining exactly the steps to do to get the tool working. Thanks for such a great video. Following all that is in there, I created a test bed and ran it in our pre-prod environment and got some results. The environment is clustered hooked up to SAN which is RAID-DP. The SQL Server is 2012 and its a VM with 32 cores and 96GB ram. Now, the results I get are 13449 TPM and 2908 NOPM running for 33 warehouses for 4 virtual users for 5 min. Are these numbers good? How do I know this is good or bad? I have not used HammerDB before, so any input is helpful. Thanks. Also, do you know any other tools for SQL Bench mark testing? I tried SQLIO on this environment and it errors and I couldnt get it to work at all. Gonna try IOMeter and see if that gives any results. Please advise on TPM and NOPM.
AMSDeveloper – unfortunately, giving personalized performance advice is a little beyond the scope of what we can do here in blog comments. If you’d like to get one-on-one personalized performance help, click Contact at the top of the blog and we can talk through what a consulting engagement looks like.
Glad the video was helpful.
Benchmarking is tricky, because many of the metrics are just “how much work can you do with a particular type of test”– and the results aren’t necessarily by themselves “good” or “bad”. HammerDB doesn’t give you scores that you can compare with official published TPC results, either. They’re really just a way of generating activity in a repeatable pattern.
Before I make any recommendations, what problem are you trying to solve? Do you have a performance problem you’re trying to identify? Or are you trying to get into a pattern of benchmarking as a good practice?
Awesome post. I don’t think that this can replace the more robust load testing that should happen in application development, but this would help immeasurably in a large number of scenarios. Have you guys ever played with playing back recorded load using SSMS?
No, there’s a lot of challenges around getting the timing correct – you need the replays hitting from multiple agents simultaneously. Check out the details behind Distributed Replay: http://technet.microsoft.com/en-us/library/ff878183.aspx
Hi there, I guess they’re calling it HammerDB lately.
I found this tool really useful. I wanted to get this tool to run a workload that I control completely but I still get to take advantage of HammerDB’s virtual users and their settings (like # of users, # of iterations and delays).
I wanted to share my “hello world” script which replaces the TPC-x benchmark script
#!/usr/local/bin/tclsh8.6
package require tclodbc 2.5.1
database connect odbc “DRIVER=SQL Server Native Client 11.0;SERVER=MSWART7\\SQL2008;PORT=1433;TRUSTED_CONNECTION=YES”;
odbc “tempdb.dbo.s_doSomething”
odbc disconnect
You can put whatever you want into that s_doSomething sproc
I’m attempting to simulate a SQL load where the application is vCenter or vCloud. Typically these applications don’t incur a heavy load, but I suppose they could if the environment was big. Anyone have any ideas on how I would simulate this load? Also how I would simulate the load growing heavier?
VirtualJake – stepping back a little, what’s your business goal in doing this testing? I’ve done a lot of work with the vCenter back end databases, and it’s not like you can tune them.
Brent,
Heh let me step back even further. I have a physical MSCS cluster supporting a vCenter deployment. I am thinking about switching to a virtualized SQL server with no MSCS clustering. Instead of MSCS I’d use VMware Fault Tolerance instead, but that feature limits the VM to 1vCPU. I’m trying to determine if a fairly large vCenter deployment (approximately 50 clusters, 200 hosts & 1000VMs) will not overload a SQL VM with 1vCPU.
Jake
Ah, gotcha – no, I wouldn’t recommend using VMware Fault Tolerance with SQL Server at this time.
Yes I know its not recommended, however I would like to try to model the load to see if would work. SRM integrated Disaster Recovery is an option, however its only an option if the components are virtual. Hence the reason why i’d like to test the load
Is it possible to try to simulate a vCenter or vCloud load? I’m assuming I could setup the schema similar to an existing database that I already have? or is it just not feasible to try to simulate?
I must be overlooking something. Where is the video itself at?
Ryan, I think the video referred to in the comments is the one on this related post: https://www.brentozar.com/archive/2012/06/demystifying-benchmarking-how-measure-performance-sql-server-video/
I wrote a blog post and then soon after a did a weekly webcast on the topic, so they got published as separate posts.
Perfect. That’s exactly what I was looking for. You mentioned it another triage Tuesday webcast too and I thought I was losing my mind not being able to find it!
Thanks!
Hi Kendra,
Hammer DB as per discussion looks good app, as I made installation for 2.16 version for Windows 32 bit system and for MYSQLServer, installation done complete as mentioned in Hammer DB install guide till Launch HammerDB window,
once I select Launch button application is not working, I could see HammerDB-2.16 folder created in Program Files, in that I selected hammerdb file as mentioned in install guide.(Even I changed first word in config file from Oracle to MYSQLServer)
could you please help me to run the application.
Hi there,
I’ve never it that error, but I’ve also never run the 32 bit version of the app or manually edited the configuration file. I suspect that changing something to say MYSQLServer would cause an error in itself: MySQL and SQL Server are two very different things.
I’m not a HammerDB specialist and I can’t really help you troubleshoot it over blog comments, but based on what you’ve said I think you should step back and make sure you’re using the right combination of products together.
Kendra
Oh man where has this been all my life. I can use it now as a DBA but i should could have used this when i was a developer. i’m definitely going to take it for a ride! Thanks Kendra!
Does Hammer DB perform load testing for my desired database say “Database A” of my preferred schema without the default schema given in the Hammer DB.
And I also need Select query to run from multiple virtual users.
If you’d like to change the behavior of HammerDB, you’re going to need to learn how its TCL scripts work. It sounds like you’ve found that’s not super simple– totally true, it’s not. If you need a tool that you can easily customize without learning a legacy scripting language, this isn’t the one!
I just started using HammerDB.
My Goal: Use this tool to get TPM, Throughput & response time from different SQL Server systems with different hardware (710,720,730,920 …) configuration and then compare those results to find which hardware performs well for sql server.
Test Case: Ran the tool on the same system and different systems with 16 users, no rampup time, 10 minutes duration.
Question: I ran the tool on the same server with same set of configuration in 3 different windows, 10mins duration for the each window and restarted SQL for each test to clear buffers and all. I have compared the three result sets, seems like stored procedures are NOT being called in the same pattern and number of CALLS for each stored procedure is DIFFERENT. In this case, how can i use this tool to compare DIFFERENT systems which has DIFFERENT hardware?
Please route me in right direction.
Thanks in advance.
Be very aware that Hammer DB dose not support case sensitve databases. It is real hassel to fix
Great article.
Task is just getting hands-on feeling as to when are results considered to be good -> i.e. relative !
I’ve tried to save a settings file, but apparently you always have to provide new values for connection, … and it has to build its database every time.
So essentially you’re always starting from scratch.
Or did I miss the feature where you can just backup a loaded database and run the benchmark with it ?
hi, i have a problem with “metrics” If I start hdbagent.bat, i get an ID. Nice.
Then I enter this ID into the options of metrics. If I start it, I get in my command window a new ID, and of course get no connection. How can I use metrics ????
thank you
Laszlo from Hungary
Laszlo – for troubleshooting help with HammerDB, you’ll want to head over to the HammerDB site.
“The user interface isn’t extremely intuitive”
Let me fix that for you…the user interface is in no way shape or form user friendly at all. If you want to waste your time divining how to flip settings back and forth instead of actually doing something then HammerDB is definitely for you!
Yeah, it’s written by coders, for coders. 😉
Please let me know is it possible that HammerDb can perform load test on my preferred schema(my database) other than the default schema given in it
Sure, just fully qualify your queries with schema names on your objects.
Is there a way to increase default size of value for each keys during the schema creation?
Ok – this is how you get HammerDB to work on case sensitive sql server’s 2012 and up – let HammerDB create the tpcc database (or whatever you named your database). Then kill the program.
Next go into the tpcc database (or whatever you named your database) and change it to ‘case insensitive’, then delete all the tables in the database and begin following the quick start guide again and it should work this time. This will allow for all the scripts inside of the HammerDB application to run seamlessly.
What is the workload percentage of DML/Select operations in hammerdb??? As calling circle swingbench benchmark percentage is like Large amounts of dynamic PL/SQL. Heavy CPU utilization • Select 83% • Insert 7% • Update 10% • Delete 0%
In HammerDb How many virtual users can i use ? Can I use 2000 Virtual Users ?
Ashish – the cool thing about open source is that it’s totally free to download it yourself and figure those things out. Go ahead and give it a shot. Enjoy!
Has anyone got an example of scheduling this using SQL Agent, Task Scheduler or a similar Windows scheduling tool. I was hoping to run this out of hours whilst the network was quiet over a number of days. I have been able to call the HammerDB CLI via a PowerShell step in SQL Agent, the tclsh86t.exe runs but doesn’t write to the temporary log even though I have specified vuset logtotemp 1 within my tcl file and can see it running. Any ideas ?
Swarchy – for general questions, head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
Thanks Brent, in the end it was just a silly typo dohh!!! I’ve been following you for a long time, love to watch your office hour videos at the weekend and all the free sp_Blitz scripts you provide :). Had been listening to some of your recent advice about getting into blogging so thought I’d have a go https://t101wilson.wordpress.com/ I’ve blogged about automating HammerDb test for some fun/ a project during lockdown. Its by no means a finished solution but hopefully it might be useful for others, or at least provide a starting point for deploying HammerDb with PowerShell to schedule benchmark tests.
Cool, I’ll add that to this week’s newsletter and hopefully that helps get the word out. Cheers!
Wow, thanks Brent that would be amazing ?
Not sure why it replaced my emoji with a question mark haha meant to be 🙂