This month blog post I would like to introduce a free OLTP benchmarking tool, the HammerDB tool. HammerDB supports TPC-C and TPC-H benchmarks. It has an intuitive GUI to let database administrators to run the benchmark test with just a few mouse clicks. I will show you how to install this tool and run a benchmark test on a SQL Server.
1. Download the HammerDB installer from the official site. (https://hammerdb.com/download.html)
2. Run the HammerDB setup.exe. Choose the installation directory (by default C:\Program Files\HammerDB-X.X), then install it.
3. For convenience, create a shortcut to the C:\Program Files\HammerDB-X.X\hammerdb.bat file, as it does not create a shortcut on the Start menu.
4. Create a placeholder database for the hammerdb benchmark. For simplicity, set the database SIMPLE recovery mode, proper initial sizing and autogrowth size, e.g.
CREATE DATABASE [HammerDB]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'HammerDB', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\HammerDB.mdf' , SIZE = 2097152KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'HammerDB_log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\HammerDB_log.ldf' , SIZE = 524288KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [HammerDB] SET RECOVERY SIMPLE
GO
5. Run hammerdb.bat to open the GUI of the benchmarking tool. You can see this tool supports Oracle, SQL Server, DB2, MySQL, PostgreSQL, and MariaDB.
6. Double-click SQL Server, select TPROC-C, click OK.
7. Expand TPROC-C, expand Schema, double click Options. Enter the SQL Server name and login information. Enter the name of the placeholder database that you just created. Select the "Number of Warehouses" and "Virtual Users", which determines the intensity of the workload.
8. Double click Build, this tool will populate the database with sample data.
9. Fix the error if you get any. (In my case, I download and install the ODBC Driver 18 for SQL Server, then run again the schema build). You should see the virtual users in the hammerdb tool are running. And in your database, there are sample tables and data populated.
10. Once all virtual users are completed, click the RED square button on the top menu bar. Which destroy the virtual users, so you can continue the next step.
11. It is recommended to backup the database, as this data population process is time consuming, and the backup gives us an exact dataset to perform future comparison.
12. Expand Driver Script, double click Option. By default, ‘Timed Driver Script’ is selected. Click OK to close this option window.
13. Expand Virtual Users, double click Option. Select the number of virtual users. Make sure NOT to check ‘Show Output’, as it can slow the actual benchmark results considerably.
14. Double click Create under Virtual User to create the virtual users for the test.
15. Double click Run under Virtual User to start the load test.
16. You can see the Timed test is running by the virtual users. Also in SSMS, you can see the tables are populating.
17. Once the test completed, you can see the Virtual User 1-MONITOR says "Test complete. TEST RESULT : System achieved XXXX NOPM from XXXX SQL Server TPM".
TPM stands for Transactions per Minute and cannot be compared between different database vendors. This value cannot be consistent between database vendors as different platforms report different transaction rate metrics, e.g. Oracle reports user commits + user rollbacks, whereas SQL Server reports Batches/sec. Also note that HammerDB reports all transactions occurring in the database and therefore if other workloads are taking place these will be shown in the TPM figure as well. For consistency across databases see NOPM. NOPM stands for New Orders per Minute and is a vendor/platform independent value extracted from the schema itself. For this reason NOPM reported at the end of a timed test is the only way to compare OLTP performance across different database platforms.
Now you can restore the database on another SQL Server, run the same test, and compare the test result to see which configuration is performance optimized.
