Running a TPC-C workload on SQL Server

I have a lot of customers with some impressive SQL Server workloads, those databases are several hundred GB large, with several hundred simultaneous users – just great environments for performance tuning and troubleshooting. Some weeks ago I thought about how I can simulate such workloads in my own environment, so that I can make my performance troubleshooting sessions and workshops more realistic and impressive. I’m not the big fan of using the (small) AdventureWorks database with some prepared queries for real performance troubleshooting. Unfortunately no one of my customers wants to share their workload and their specific problems with me and the whole other world in public 😉

Because of that I was looking in the last days for some free tools, with which I can simulate large OLTP workloads with hundreds of different users, and where I’m also able to scale out the workload to thousands of users – if I want and I have the requirement to do it. During the weekend I finally found one very interesting tool that is free and simulates a whole TPC-C workload: it’s called Hammerora and it is a free Open Source Oracle Load Test Tool that simulates an OLTP based TPC-C workload. Yes, you read correct: the tool is written for Oracle, but fortunately they also support SQL Server! You can find the tool at http://hammerora.sourceforge.net. In this weblog posting I want to give you a quick overview about the usage of the tool, and how easy it is to run a whole TPC-C workload on your own SQL Server within a few minutes.

After the installation of the tool, you can start it through the provided batch file hammerora.bat, which is stored in the installation directory.

When you want to simulate a TPC-C based workload, you have to do 2 different things:

• Creating the necessary database with the initial data
• Run the TPC-C against the created database

Let’s have a more detailed look on both of these steps. Before you can create the actual database, you have to tell the tool with which database system you are working. Hammerora supports the following database systems:

• Oracle
• MySQL
• Microsoft SQL Server

You can set your actual database through the menu option Benchmark/Benchmark Options:

When you are working with Oracle or MySQL, Hammerora also supports a TPC-H (Data Warehouse) based workload. After you have set your database system to SQL Server, you can go to the menu Benchmark/TPC-C/TPC-C Schema Options.

This dialog allows you to set all the necessary options that are needed by Hammerora to create the actual TPC-C database schema. In the first part of the dialog you can specify your SQL Server connection info, and in the following part you can define the number of warehouses (parameter Number of Warehouses) that Hammerora creates in the database. You can find more detailed information about the database structure on the official TPC website at http://tpc.org/tpcc/detail.asp, which also describes the concept of a warehouse that is used by the TPC-C benchmark. With the parameter Virtual User to Build Schema you can specify how many sessions Hammerora should use during the creation of your database. You can think of a Virtual User as a session in SQL Server. This option allows you to create your TPC-C database in parallel with simultaneous sessions. When you have specified the necessary configuration options, you are ready to create your database. You just have to hit the Create TPC Schema symbol in the toolbar (12th symbol from left.). The creation of one warehouse needs around 130MB in the database. So it’s also very easy to create a database with several hundred GBs of data in it – just increase the number of warehouses, but trust – it will take some time! J

After the creation of the TPC-C database, you have to configure which kind of test suite Hammerora has to execute against your database. The test suite itself is implemented as a so-called Driver Script. When you go again to the menu Benchmark/TPC-C/TPC-C Schema Options, you can see that Hammerora supports 2 kinds of driver scripts:

• Standard Driver Script
• Timed Test Driver Script

The Standard Driver Script just executes a continuous workload against your SQL Server database: when one query is finished the next query is submitted without any pausing. Your SQL Server will be hammered with a continuous workload, and how can see how far your SQL Server installation will scale. With the Timed Test Driver Script Hammerora introduces so-called Keying- and Thinking Times, which are also part of the official TPC-C benchmark. With this option, Hammerora submits about 3 queries per minute to your SQL Server, because Hammerora tries to simulate real users, which have to key in their data (Keying Time) and need some time for thinking (Thinking Time). With the Timed Test Driver Script you need a very high amount of Virtual Users to get a reasonable workload on your SQL Server. Further information about the various options that the Timed Test Driver Scripts accepts can be also found in the documentation of Hammerora.

For my scenarios I have just used the Standard Driver Scripts, because I want to have a continuous workload against SQL Server, so that I can demonstrate various performance tuning and troubleshooting techniques. When you have selected the corresponding driver script, you have to generate the actual script that is used for creating the workload against SQL Server. You can generate that script by using the menu option Benchmark/TPC-C/TPC-C Driver Script. When you have selected that option, Hammerora shows the driver script as an output in the main window:

The only thing that you finally have to configure is the amount of Virtual Users that Hammerora uses to execute the Driver Script against your SQL Server database. You can set the amount of Virtual Users through the menu option Virtual Users/Vuser Options:

As a last step you need to create those Virtual Users inside Hammerora by using the symbol Create Virtual Users (10th symbol from left) from the toolbar. You can see the created Virtual Users in the middle part of the main window. Finally you can use the symbol Run Hammerora Loadtest (11th symbol from left) from the toolbar to start the TPC-C workload against SQL Server. It takes some seconds until Hammerora has created all the necessary sessions, but then the actual TPC-C workload is executed against SQL Server:

As you can see from this weblog posting, it is very easy to create an OLTP based workload that is executed against SQL Server. With this approach it is also very easy to demonstrate some key performance problems inside SQL Server like incorrect Transaction Log usage, Locking/Blocking scenarios, Memory Management, misconfigured I/O subsystems etc.

On the other hand you can also demonstrate some other advanced concepts like Clustering, Database Mirroring, Log Shipping, Replication etc. with real workloads, and how some features affects the availability of your database. The possible scenarios are just endless, and you are just working with a real OLTP workload which can scale very easily to thousands of concurrent users, if you want and you have the resources… I think this approach can give our conference sessions and workshops a new feeling when we demonstrate SQL Server concepts and performance troubleshooting J

Thanks for reading

-Klaus