How to Run SQL Server Load Tests Using HammerDB

SqlBak. HammerDBRecently we ran load tests against our SQL Server to see how server performance changes and how SQL Server responds to the load. If you’re interested here you can find a tutorial on how to simulate load against a database using free SQL Server load simulator HammedDB.

How to Configure HammerDB

1. Choose the Benchmark

First you need to select a benchmark. To do it go to “Options” and select “Benchmark”,  then when prompted select MSSQL Server and TPC-C. Then press OK. SQL Server Load Test Monitoring HammeDB step 1

2. Create and Run SQL Server Test Schema

Now it is necessary to create SQL Server test schema. Click on the Benchmark tree view, click TPC-C, then select “Schema Build” and choose “Options”.  When prompted, enter the connection details of your SQL Server, choose a database name – this may be an existing empty database that you have already created or if it does not exist HammerDB will create it during the installation. Then set a number of warehouses with the slider (we selected 8) and do not forget to select the Virtual Users. Click OK.

Press “Build” button to start schema creation. When all users are successfully created the schema build is finished, click red traffic light icon.SQL Server Load Test Monitoring HammeDB step 2

3. Tune and Load Driver Script

The next step which you have to follow is to set and load driver script. To do that, select “Driver Script” under the “Benchmark” tree view and click “Options”. Accept all default settings, click OK.  To run the script double-click on “Load” button.SQL Server Load Test Monitoring HammeDB step 3

4. Start the Autopilot

We are almost ready to start loading SQL Server. Let’s do it with the help of “Autopilot mode”. Select “Autopilot” under the “Benchmark” tree view and click “Options”. When prompted, set simple settings and press “OK” to apply changes. To run the test click “Autopilot”. SQL Server Load Test Monitoring HammeDB step 4

How to Check SQL Server CPU Loading

Now HammerDB will start simulating OLTP (Online transaction processing) database load. You can check how SQL Server loads the CPU of  your server using the following T-SQL script:

DECLARE @CPU_BUSY int, @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE 
WAITFOR DELAY '00:00:00:500'
SELECT(@@CPU_BUSY - @CPU_BUSY) / ((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) * 100 AS CPUBUSY

The SSMS (SQL Server Management Studio) will show how your CPU is busy with SQL Server in percentage terms. CPU is busy with SQL Server

From this result we can see that our server’s CPU is occupied with SQL Server by 55.17%. Now let’s stop all testing transactions (by clicking the red traffic light on HammerDB) and check how busy your CPU is. You will receive the following result:CPU isn't busy with SQL ServerIt means that SQL Server does not load your CPU at all, the CPUBUSY is 0%.

It is a good practice to check how busy is your CPU with SQL Server as one day server can stop responding because of SQL Server load and your data might be lost.

Leave a Reply

Your email address will not be published. Required fields are marked *