To monitor all the SQL MIs simultaneously, HammerDB CLI automation was used. This section describes how the auto build and auto drive dictionary parameters for the testing are configured. 

Extensive consideration for the proper, real-life database configuration was analyzed. The goal was to achieve a balance between performance and ease of deployment, such as required from a DBaaS solution.               

HammerDB OLTP database configuration (repeated for every environment and for every test)

• 300 Warehouses created with 30 users
• Roughly 30 GB in size (each HammerDB warehouse contains about 100 MB of data)
• Databases are restored to original 300 Warehouse state before each test group execution
• The database has pre-sized data and log files to remove SQL auto growth contention.

Table 6.      SQL table row counts for fully prepared – 300 warehouses OLTP database

Note: The above metrics gave the TPM results that are 50% of the HammerDB tests with 12x database size or 4000 warehouses (500 GB). As the Hammer DB testing with the maximum warehouses does not fit in to a true DBaaS mindset. We used a more streamlined approach that gave excellent TPM numbers. 

Auto build configuration

dbset db mssqls

diset connection mssqls_server 192.168.101.xxx

diset connection mssqls_tcp true

diset connection mssqls_port 1433

diset connection mssqls_authentication sql

diset connection mssqls_odbc_driver ODBC Driver 17 for SQL Server

diset tpcc mssqls_count_ware 300

diset tpcc mssqls_num_vu 30 

vuset logtotemp 1

Auto drive configuration

dbset db mssqls

diset connection mssqls_server 192.168.101.xxx

diset connection mssqls_tcp true

diset connection mssqls_port 1433

diset connection mssqls_authentication sql

diset connection mssqls_odbc_driver ODBC Driver 17 for SQL Server

diset connection mssqls_keyandthink false

diset tpcc mssqls_total_iterations = 1000000

diset tpcc mssqls_driver timed

diset tpcc mssqls_rampup 1

diset tpcc mssqls_duration 9

diset tpcc mssqls_checkpoint true

diset tpcc mssqls_allwarehouse true

diset tpcc mssqls_timeprofile true

Autopilot

The total duration run time, per virtual user, is 11 minutes. Autopilot was configured as follows.

loadscript

puts "SEQUENCE STARTED"

foreach z { 40 80 120 160 200 } {

puts "$z VU TEST"

vuset vu $z

vucreate

puts "TCOUNTER STARTED"

tcstart

tcstatus

vurun

#Runtimer, set in seconds, must exceed rampup + duration 660 = 11 min

#Rampup 1, Duration 9, 1 extra minute to capture TPM

runtimer 660

vudestroy

after 5000

}