HammerDB

In this task, we will use the HammerDB an open source tool and it works well to demonstrate the performance of the SQL Server database.

There are many load testing tools available which includes free, open source and licensed tools. In this workshop we will use the HammerDB an open source tool and it works well to demonstrate the performance of the SQL Server database. HammerDB is an open source database load testing and benchmarking tool for Oracle, SQL Server, PostgreSQL, MySQL. It should also work with Aurora and MariaDB, and will work on Redshift with a minor modification of the benchmark script.

Configuring SQL Server for load testing

In this step we will create the database for the load testing and configure the SQL account for the HammerDB.

  1. Connect to SQL Server Instance and run the below query to create the database
USE master
GO

CREATE DATABASE [tpcc]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'tpcc', FILENAME = N'D:\DATA\tpcc.mdf' ,
SIZE = 10485760KB , FILEGROWTH = 524288KB )
LOG ON
( NAME = N'tpcc_log', FILENAME = N'D:\LOG\tpcc_log.ldf' ,
SIZE = 1048576KB , FILEGROWTH = 524288KB )
GO

ALTER DATABASE [tpcc] SET RECOVERY SIMPLE
GO
  • Execute below query to change the SQL Server authentication to mixed mode authentication
EXEC master.sys.xp_loginconfig 'mixed'
  • Restart the SQL Server Service
  • Create the SQL login using below script
USE [master]
GO
CREATE LOGIN [hamdb1] WITH PASSWORD=N'AWS@1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [hamdb1]
GO

Installing HammerDB and SQL Server Native client tools

  1. Launch the EC2 instance as the similar instance type as SQL Server
  2. Configure the security group to allow connection to SQL Server
  3. Download & Install the HammerDB-2.20 and SQL Server Native Client tool. You can download it from http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409 and http://www.hammerdb.com/download.html

Configuring and Running HammerDB

  1. If you don't see the HammerDB GUI or dialog box, you can run the batch file for the GUI (C:\Program Files\HammerDB-2.20\hammerdb.bat)
  2. Double-click SQL Server in the Benchmark panel.
  3. Choose TPC-C, an acronym that stands for: Transaction Processing Performance Council - Benchmark C

  1. In the Benchmark panel, next to SQL Server, click the + to expand the options.
  2. Below TPC-C, click Schema Build and then double click Options.
  3. Specify the details of SQL Server IP address, username, and password to connect

  1. For the Schema option, choose Updated, which creates a better TPC-C schema with more appropriate structure and better indexes.
  2. In this case, the Number of Warehouses (the scale) is set to 1 and virtual users to build schema to 1. You can set the different limit for the actual production workload. Some guidelines suggest 10 to 100 warehouses per CPU. For Virtual Users to Build Schema, choose a number that is between 1- and 2-times the number of client vCPUs. You can click the grey bar next to the slider to increment the number.
  3. Click OK
  4. Double click the Build option below the Schema Build section to create the schema and load the tables. When that completes, click the red flash light icon in the top center of the screen to destroy the virtual user and move to the next step.

Creating the driver script

  1. HammerDB uses the driver script to orchestrate the flow of SQL statements to the database to generate the required load.
  2. In the Benchmark panel, expand the Driver Script section and double-click Options.
  3. Verify the settings match what you used in the Schema Build dialog.
  4. Choose Timed Test Driver Script.
  5. The Checkpoint when complete option forces the database to write everything to disk at the end of the test, so check this only if you plan on running multiple tests in a row.
  6. To ensure a thorough test, set Minutes of Rampup Time to 2 and Minutes for Test Duration to 10.

  1. Click OK to exit the dialog.
  2. Double-click Load in the Driver Script section of the Benchmark panel to activate the driver script.

Creating virtual users

Creating a real scenario kind of load typically requires running scripts as multiple different users. Create some virtual users for the test.

  1. Expand the Virtual Users section and double click Options.
  2. If you set your warehouse count (scale) to 1, then set the Virtual Users to 1, because the TPC-C guidelines recommend a 10x ratio to prevent row locking. Select the Show Output checkbox to enable error messages in the console.
  3. Click OK

Autopilot mode

Most of the time we will execute multiple test cases where only the number of virtual users may vary. HammerDB utility gives you options such as creating virtual users as mentioned in the previous step or you can use the AutoPilot mode of HammerDB.

  1. To enable the Autopilot mode expand AutoPilot node and double click on Options

  1. As we have a ramp-up of 2 minutes and test duration set to 8 minutes, we will set minutes per test value to 15 to be sure everything is ok.

3 5 9 17 25 37 49 65 97 129 197 257

  1. Double click on AutoPilot

You can also download the procedures like https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/blob/dev/sp_Blitz.sql and schedule job to schedule the statistics.