I currently have a presentation named Performance Tuning for Pirates. This presentation goes over using several free tools to help you with your performance tuning process. Today, I am happy to share my first video going over these tools.
Using SQLQueryStress to stress test Stored Procedures
Free tools for SQL Server from Redgate - search your databases quickly in SSMS, and get access to incredibly useful scripts from SQL Server experts. SQL Server Stress Test tool. Eye on SQL - Load Test is a tool designed for volume tests against SQL Server. Once started, you can start a new test where you can define the amount of concurrent connections, if the load is all at once or by increments, the duration and if the test is Continuous (in which case the previous settings defaults to predefined values).
SQLQueryStress by Adam Machanic (blog | twitter) is one of my favorite tools for doing a quick stress test against a user object like a stored procedure. This tool can actually be used to do stress testing on T-SQL queries but I usually use it for compiled objects like stored procedures. I love this tool because it allows you to save and load configurations to quickly redo tests. It also allows you to use a dataset for input parameters and allows you to add additional stress with configuring iterations and threads.
Note: It also comes in handy when you need to mock up some stress for demos.
I also written a tip for www.mssqltips.com that goes over a step by step guide on how to use SQLQueryStress.
You Need To Test Your SQL Server!
Its like screaming “TEST THE BREAKS ON YOUR CAR!” If you don’t have the tools or know how it is just good advice you can’t follow. I’ve done a TON of benchmarks over the years using every tool imaginable including ones I’ve written myself. There are several barriers to generating a valid repeatable benchmark in your own environment. Lets take a look at the general criteria that I use when testing OLTP systems and the benchmark tools available.
I’ve Been Spoiled
I’m going to come clean. I really like Quest Software’s Benchmark Factory. They have put years of work into the tool set. It is also one of the few database benchmark tools that runs against most major RDBMS platforms. This alone made it my go to tool for a very long time. Also, since it implements most of the TPC Council benchmark specifications when someone asks how I tested I can hand them reams of information on the benchmark in question. TPC also has a well defined metric for determining not just raw performance but a dollar amount to get that performance. The catch is that Benchmark Factory isn’t an apples to apples with the published TPC results. To assume so would be a horrible mistake on your part. If you have ever read through a full disclosure (I read them to my four year old to put him to bed) you know that it would be very difficult for your organization to implement the exact same test on the exact same software without spending some major bucks. I have worked a few projects that were willing to spend the money and bring in a team to implement the testing protocols. If you are talking a project that is going to run say ten million your boss may be willing to spend 100,000 of that to completely test the system before it goes into production. In most cases though, this isn’t the case. Even spending the money on Benchmark Factory may be outside of your budget. So, we turn to free or opensource solutions sometimes we just write our own.
Other Options
There are several other options for testing database servers. I’ve use a few over the years. First, I’m limiting this list to those that run on Windows, they may run on other platforms but that isn’t important to me. Secondly, The test setup has to be repeatable. Lastly, there has to be a way to measure performance. In some cases this may be using perfmon(Windows Performance Monitor) or the tool may capture some meaningful metrics.
Database Hammer SQL Server Resource Kit
http://www.sqlmag.com/content1/topic/put-the-hammer-down/catpath/configuration
Not exactly free or cutting edge but was good a putting a load on SQL Server. Even though this is VERY old due to its simplicity it works pretty much on every version of SQL Server from 2000 on. Again, one of the downsides is capturing metrics. The other down side is finding the dang thing. If you have an MSDN subscription you can still download it or you can head over to your local used book store and hope the CD-Rom that came with the book isn’t toast.
Eye On SQL – Load Test
http://eyeonsqlloadtest.codeplex.com/
A basic tool to put a database under load. There are no predefined tests and it doesn’t gather more than the most basic metrics. It is simple to use and can be a replacement for something like Database Hammer
TPCBench
https://github.com/SQLServerIO/TPCCBench
Free SQL Server Performance Testing Utilities – SQLServerCentral
This is a tool I wrote a while back that implements TPC-C. It is in C# so anyone that is familiar can work on the code and peek inside to see how I implemented my version. The down side is it only implements one test, TPC-C. and TPC-C has several limiting factors to it. The one I never liked was all the data generated was random, all of it. If it was a name it was a random string. This leads to a database that effectively has 100% carnality, and is also a very poor choice for testing database compression. My goal was to implement TPC-E, which overcomes all of these issues but is fairly complex and hard to get right. I fell back to Benchmark Factory and never moved forward with this tool.
Sql Server Stress Test Tool Free Online
Hammerora
http://hammerora.sourceforge.net/
This is a pretty simple to use tool an has a easy to understand GUI. It supports two benchmarks a TPC-C style OLTP benchmark and a TPC-H style OLTP benchmark. That is also one of the down sides, it only supports two benchmarks and both are a bit long in the tooth. It also supports various flavors of SQL Server, Oracle, MySQL and Postgres. This is a nice little bonus if you are testing like hardware and OS and want to vary the RDBMS. The last bonus for me personally is it is opensource. This means I can look at the code and see how each test is implemented.
DVD Store Database Benchmark
http://en.community.dell.com/techcenter/extras/w/wiki/dvd-store.aspx
This is a tool I’m currently working on to bridge the gap between TPC-C and TPC-E. It was written in house by Dell and now is developed and maintained by Dell and VMWare. It is more complex than TPC-C but not as complex as TPC-E. It is opensource which was a huge plus for me and allowed me to fork it over at https://github.com/SQLServerIO/DVD-Store-Database-Benchmark and tweak on it to meet my needs. I’ll have a full blog post on this soon explaining the benchmark in detail and what changes I have made to it. It is a staple for testing databases on ESX. It has a standard way for gathering metrics. The load generator is actually in C#. You can also do a full stack test since it will work against a website that is also included in the benchmark. The down side again, is it only implements one benchmark.
Description of the Replay Markup Language (RML) Utilities for SQL Server
http://support.microsoft.com/kb/944837
This is a tool set that was initially developed in house at Microsoft to test customer scenarios. This is different than the other tools since it is a replay tool. But you can capture metrics and vary your workloads. The big down side is it isn’t easy to use. Also, if you are testing new hardware and don’t have a trace to replay from production you are back to zero.
Apache JMeter
http://jmeter.apache.org/
Not a database stress tool in the strictest since of the word but you can use it that way. The upside for JMeter is it will test your whole stack, web servers, middle tiers and database servers. You can use it to drive pretty much anything. Since it is written if Java it runs on Windows. Since there is a JDBC driver for SQL Server you can use it directly to test your database as well. The down sides are also huge. It is written in Java for starters. It is also using the JDBC stack and that can be a limiting factor. It is a significant undertaking to setup as well. I’ve included some links to people that have set it up to test databases and a ASP.NET web site.
Now What?
Well, that is easy, start reading, building and testing. I personally use something like Eye On SQL or Hammerora to do an initial smoke test or break in. Then move over to the more complicated benchmark tools. I have pretty much settled on the DVD Store benchmark for now as my OLTP testing tool. Now I need to find something more up to date than the TPC-H test everyone else has implemented so I can round out with a solid OLAP benchmarking tool. Maybe I’ll write something again!