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