Recently 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. 

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 the “Build” button to start schema creation. When all users are successfully created the schema build is finished, click red traffic light icon.

3. Tune and Load Driver Script

The next step which you have to follow is to set and load the 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 the “Load” button.

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”. 

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. 

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: It 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 a one-day server can stop responding because of SQL Server load and your data might be lost.