Skip to content

nocentino/hammerdb

BranchesTags

Repository files navigation

HammerDB Benchmark Scripts for SQL Server

This repository contains automated HammerDB benchmark scripts for running TPC-C (TPROC-C) and TPC-H (TPROC-H) workloads against Microsoft SQL Server using Docker containers.

Full blog post here: https://www.nocentino.com/posts/2025-09-06-hammerdb-containers/

Overview

The scripts provide a streamlined way to:

  • Automatically set up SQL Server 2025 container
  • Build TPC-C and TPC-H schemas
  • Run benchmark tests with configurable parameters
  • Extract and format test results

All configuration is managed through environment variables, making it easy to adjust parameters without modifying the scripts.

Prerequisites

  • Docker and Docker Compose installed
  • Sufficient disk space for SQL Server containers and test databases

Project Structure

hammerdb/
├── hammerdb.env                    # Environment configuration file
├── docker-compose.yml              # Docker Compose configuration
├── loadtest.sh                     # Main execution script
├── scripts/
│   ├── build_schema_tprocc.tcl    # Build TPC-C schema
│   ├── build_schema_tproch.tcl    # Build TPC-H schema
│   ├── load_test_tprocc.tcl       # Run TPC-C benchmark
│   ├── load_test_tproch.tcl       # Run TPC-H benchmark
│   └── generic_tprocc_result.tcl  # Extract TPC-C results
├── output/                         # Test results directory (created automatically)
└── README.md                       # This file

Getting Started: The 5-Minute Setup

# Clone the repository
git clone https://github.com/nocentino/hammerdb.git
cd hammerdb

# Configure for your environment
cp hammerdb.env.example hammerdb.env

# Run everything, build, load, and parse. 
./loadtest.sh

Configure your test parameters

Once you have the environment up and running, now its time to customize it for your environment. Edit hammerdb.env to match your requirements. See Configuration section for details.

Running Individual Components

This environment consists of two main components: a 2025 test container, and a containerized HammerDB implementation. For a quick start, you can launch the SQL Server 2025 container and run the tests shown below. After familiarizing yourself with the test environment, you can modify hammerdb.env to target any SQL Server instance on your network by changing the SQL_SERVER_HOST environment variable and execute load tests against production or staging systems. Be sure to adjust the configuration parameters as documented in the Configuration section below.

Start SQL Server Container

SQL Server 2025 RC0 on port 4001

docker run \
    --env 'ACCEPT_EULA=Y' \
    --env 'MSSQL_SA_PASSWORD=S0methingS@Str0ng!' \
    --name 'sql_2025' \
    --volume sqldata_2025:/var/opt/mssql \
    --publish 4001:1433 \
    --platform=linux/amd64 \
    --detach mcr.microsoft.com/mssql/server:2025-RC1-ubuntu-24.04

Run HammerDB Tests with Docker Compose

The HammerDB test execution is orchestrated through Docker Compose using environment variables to control the test mode and benchmark type. Each benchmark follows a three-phase process: schema building, load testing, and results parsing. The RUN_MODE variable determines which phase to execute (build, load, or parse), while the BENCHMARK variable specifies whether to run TPC-C (tprocc) or TPC-H (tproch) workloads. This modular approach allows you to run specific test phases independently or chain them together for complete benchmark execution and testing multiple configurations iteratively.

Note: Schema building is a one-time operation per benchmark configuration and test size dimension. Once built, you can execute multiple load tests and parse results without rebuilding the schema, making iterative testing and configuration tuning more efficient.

# TPC-C Schema Build
RUN_MODE=build BENCHMARK=tprocc docker compose up

# TPC-C Load Test
RUN_MODE=load BENCHMARK=tprocc docker compose up

# TPC-C Results Parsing (use --no-TTY flag if output is getting truncated)
docker compose run --rm --no-TTY -e RUN_MODE=parse -e BENCHMARK=tprocc hammerdb

# TPC-H Schema Build
RUN_MODE=build BENCHMARK=tproch docker compose up

# TPC-H Load Test
RUN_MODE=load BENCHMARK=tproch docker compose up

# TPC-H Results Parsing (use -T flag if output is getting truncated)
docker compose run --rm --no-TTY -e RUN_MODE=parse -e BENCHMARK=tproch hammerdb

Tip: If you're experiencing truncated output during the parse phase, use the --no-TTY flag to disable pseudo-TTY allocation, which provides raw unbuffered output.

Configuration

All configuration is managed through the hammerdb.env file. Below are the expose configration environment variables.

Environment Variables

Database Connection

  • USERNAME: SQL Server username (default: sa)
  • PASSWORD: SQL Server password (default: S0methingS@Str0ng!)
  • SQL_SERVER_HOST: SQL Server host and port (default: localhost,4001)

Common Settings

  • USE_BCP: Enable BCP for faster data loading (true/false)
  • TMPDIR: Directory for temporary files and output (default: /tmp)
  • MSSQLS_TCP: Use TCP connection (default: true)
  • MSSQLS_AUTHENTICATION: Authentication type (default: sql)

TPROC-C (TPC-C) Configuration

Schema Build Settings:

  • TPROCC_DATABASE_NAME: Database name for TPC-C (default: tpcc)
  • TPROCC_DRIVER: Database driver (default: mssqls)
  • TPROCC_BUILD_VIRTUAL_USERS: Virtual users for schema build
  • WAREHOUSES: Number of warehouses
  • TPROCC_DRIVER_TYPE: Driver type (timed/test)
  • TPROCC_ALLWAREHOUSE: Use all warehouses in test (true/false)

Test Settings:

  • VIRTUAL_USERS: Virtual users for test execution
  • RAMPUP: Ramp-up time in minutes
  • DURATION: Test duration in minutes
  • TOTAL_ITERATIONS: Total iterations to run
  • TPROCC_LOG_TO_TEMP: Log output to temp directory (0/1)
  • TPROCC_USE_TRANSACTION_COUNTER: Enable transaction counter (true/false)
  • TPROCC_CHECKPOINT: Enable checkpoint during test (true/false)
  • TPROCC_TIMEPROFILE: Enable time profiling (true/false)

TPROC-H (TPC-H) Configuration

Schema Build Settings:

  • TPROCH_DATABASE_NAME: Database name for TPC-H (default: tpch)
  • TPROCH_DRIVER: Database driver (default: mssqls)
  • TPROCH_SCALE_FACTOR: Scale factor for data generation
  • TPROCH_BUILD_THREADS: Number of threads for schema build
  • TPROCH_USE_CLUSTERED_COLUMNSTORE: Use clustered columnstore indexes (true/false)

Test Settings:

  • TPROCH_VIRTUAL_USERS: Virtual users for test execution
  • TPROCH_TOTAL_QUERYSETS: Number of query sets to run
  • TPROCH_MAXDOP: Maximum degree of parallelism for queries
  • TPROCH_LOG_TO_TEMP: Log output to temp directory (0/1)

Recommended Configuration for Different System Sizes

Each configuration below provides a complete hammerdb.env file tailored for different hardware specifications.

The smallest test you can run.

Below is a configuration that provides the smallest possible test setup for quickly validating your environment. Use this when you want to verify everything is working correctly without waiting for lengthy database builds or extended test runs. This minimal setup creates a 100MB database and completes testing in under 5 minutes. This is what's included in the repository. More realistic examples are below in the readme. Update your hammerdb.env with these examples and modify them for your hardware.

# Database Connection
USERNAME=sa
PASSWORD=S0methingS@Str0ng!
SQL_SERVER_HOST=localhost,4001

# Common settings for all benchmarks
USE_BCP=true
TMPDIR=/tmp

# Connection settings
MSSQLS_TCP=true
MSSQLS_AUTHENTICATION=sql

# TPROC-C Configuration
TPROCC_DATABASE_NAME=tpcc
TPROCC_DRIVER=mssqls

# TPROC-C Build settings
TPROCC_BUILD_VIRTUAL_USERS=1
WAREHOUSES=1
TPROCC_DRIVER_TYPE=timed
TPROCC_ALLWAREHOUSE=true

# TPROC-C Test settings
VIRTUAL_USERS=1
RAMPUP=0
DURATION=1
TOTAL_ITERATIONS=10000000
TPROCC_LOG_TO_TEMP=0
TPROCC_USE_TRANSACTION_COUNTER=true
TPROCC_CHECKPOINT=false
TPROCC_TIMEPROFILE=true

# TPROC-H Configuration
TPROCH_DATABASE_NAME=tpch
TPROCH_SCALE_FACTOR=1
TPROCH_DRIVER=mssqls
TPROCH_BUILD_THREADS=1
TPROCH_USE_CLUSTERED_COLUMNSTORE=true

# TPROC-H specific test settings
TPROCH_VIRTUAL_USERS=1
TPROCH_TOTAL_QUERYSETS=1
TPROCH_MAXDOP=8
TPROCH_LOG_TO_TEMP=1

8-Core System with 24GB RAM

This configuration is optimized for a typical development/test workstation:

# Database Connection
USERNAME=sa
PASSWORD=S0methingS@Str0ng!
SQL_SERVER_HOST=localhost,4001

# Common settings for all benchmarks
USE_BCP=true
TMPDIR=/tmp

# Connection settings
MSSQLS_TCP=true
MSSQLS_AUTHENTICATION=sql

# TPROC-C Configuration
TPROCC_DATABASE_NAME=tpcc
TPROCC_DRIVER=mssqls

# TPROC-C Build settings
TPROCC_BUILD_VIRTUAL_USERS=4    # Half your cores for parallel loading
WAREHOUSES=50                   
TPROCC_DRIVER_TYPE=timed
TPROCC_ALLWAREHOUSE=true

# TPROC-C Test settings  
VIRTUAL_USERS=16                # 2x cores
RAMPUP=2                        # 2 minutes to stabilize
DURATION=10                     # 10 minutes for meaningful results
TOTAL_ITERATIONS=10000000   
TPROCC_LOG_TO_TEMP=0
TPROCC_USE_TRANSACTION_COUNTER=true
TPROCC_CHECKPOINT=false
TPROCC_TIMEPROFILE=true

# TPROC-H Configuration
TPROCH_DATABASE_NAME=tpch
TPROCH_DRIVER=mssqls
TPROCH_SCALE_FACTOR=10          # 10GB dataset
TPROCH_BUILD_THREADS=4          # Half your cores
TPROCH_USE_CLUSTERED_COLUMNSTORE=true

# TPROC-H Test settings
TPROCH_VIRTUAL_USERS=4          # Lower for CPU-intensive queries
TPROCH_TOTAL_QUERYSETS=1        # One complete run
TPROCH_MAXDOP=8                 
TPROCH_LOG_TO_TEMP=1

4-Core System with 16GB RAM - a really small VM, either on-prem or in the cloud

Optimized for smaller development systems or cloud instances:

# Database Connection
USERNAME=sa
PASSWORD=S0methingS@Str0ng!
SQL_SERVER_HOST=localhost,4001

# Common settings for all benchmarks
USE_BCP=true
TMPDIR=/tmp

# Connection settings
MSSQLS_TCP=true
MSSQLS_AUTHENTICATION=sql

# TPROC-C Configuration
TPROCC_DATABASE_NAME=tpcc
TPROCC_DRIVER=mssqls

# TPROC-C Build settings
TPROCC_BUILD_VIRTUAL_USERS=2    # Half your cores
WAREHOUSES=30                   # ~3GB database
TPROCC_DRIVER_TYPE=timed
TPROCC_ALLWAREHOUSE=true

# TPROC-C Test settings
VIRTUAL_USERS=8                 # 2x cores
RAMPUP=2                        # 2 minutes to stabilize
DURATION=10                     # 10 minutes for testing
TOTAL_ITERATIONS=10000000       
TPROCC_LOG_TO_TEMP=0
TPROCC_USE_TRANSACTION_COUNTER=true
TPROCC_CHECKPOINT=false
TPROCC_TIMEPROFILE=true

# TPROC-H Configuration
TPROCH_DATABASE_NAME=tpch
TPROCH_DRIVER=mssqls
TPROCH_SCALE_FACTOR=5           # 5GB dataset
TPROCH_BUILD_THREADS=2          # Half your cores
TPROCH_USE_CLUSTERED_COLUMNSTORE=true

# TPROC-H Test settings
TPROCH_VIRTUAL_USERS=2          # Conservative for small systems
TPROCH_TOTAL_QUERYSETS=1        # One complete run
TPROCH_MAXDOP=4                 # Use all cores
TPROCH_LOG_TO_TEMP=1

16-Core System with 64GB RAM - A moderatly sized system

Configuration for production-grade servers or high-performance workstations:

# Database Connection
USERNAME=sa
PASSWORD=S0methingS@Str0ng!
SQL_SERVER_HOST=localhost,4001

# Common settings for all benchmarks
USE_BCP=true
TMPDIR=/tmp

# Connection settings
MSSQLS_TCP=true
MSSQLS_AUTHENTICATION=sql

# TPROC-C Configuration
TPROCC_DATABASE_NAME=tpcc
TPROCC_DRIVER=mssqls

# TPROC-C Build settings
TPROCC_BUILD_VIRTUAL_USERS=8    # Half your cores
WAREHOUSES=200                  # ~20GB database
TPROCC_DRIVER_TYPE=timed
TPROCC_ALLWAREHOUSE=true

# TPROC-C Test settings
VIRTUAL_USERS=32                # Start with 2x cores
RAMPUP=3                        # 3 minutes for larger scale
DURATION=15                     # 15 minutes for stable results
TOTAL_ITERATIONS=10000000       # Effectively unlimited
TPROCC_LOG_TO_TEMP=0
TPROCC_USE_TRANSACTION_COUNTER=true
TPROCC_CHECKPOINT=false
TPROCC_TIMEPROFILE=true

# TPROC-H Configuration
TPROCH_DATABASE_NAME=tpch
TPROCH_DRIVER=mssqls
TPROCH_SCALE_FACTOR=30          # 30GB dataset
TPROCH_BUILD_THREADS=8          # Half your cores
TPROCH_USE_CLUSTERED_COLUMNSTORE=true

# TPROC-H Test settings
TPROCH_VIRTUAL_USERS=8          # More parallelism
TPROCH_TOTAL_QUERYSETS=1        # One complete run
TPROCH_MAXDOP=8                 # Use all cores
TPROCH_LOG_TO_TEMP=1

Understanding the Results

The framework automatically extracts key metrics, including:

TPC-C Output:

  • Transactions Per Minute (TPM)
  • New Orders Per Minute (NOPM)
  • Response time percentiles

TPC-H Output:

  • Individual query execution times
  • Total runtime for all 22 queries
  • Query-specific metrics

Results are saved in both raw format (logs) and parsed format in the output/ directory.

Troubleshooting

To start the container in interactive mode, useful for debugging tests.

# if the container isn't built yet
docker compose build

# this will enter an interactive terminal inside the hammerdb container
docker run -it --network host \
  --env-file hammerdb.env \
  --env RUN_MODE=parse \
  --env BENCHMARK=tprocc \
  --env TMP=/tmp \
  -v $(pwd)/scripts:/opt/HammerDB-5.0/scripts \
  -v $(pwd)/output:/tmp \
  --entrypoint /bin/bash \
  hammerdb-hammerdb:latest

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

8 stars

Watchers

1 watching

Forks

1 fork
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages