Home > Workload Solutions > SQL Server > White Papers > Scaling SQL Server 2022 VMs on Dell Integrated System for Microsoft Azure Stack HCI > Benchmark testing methodology
Our methodology to scale SQL Server 2022 on the Dell Integrated System for Microsoft Azure Stack HCI included setting up a benchmarking environment. On our 4-node setup, we created an environment which included VMs running with SQL Server 2022 installed on Windows Server 2022.
HammerDB is an open-source tool used for benchmarking different types of database solutions by simulating virtual users to perform transactions against the database selected. For more information, see the HammerDB website.
For each VM with SQL Server 2022 running, we installed and configured HammerDB instances on a cluster of clients running Windows Server 2022 on VMs. The client cluster also hosted our management applications like Domain Name System (DNS), Dynamic Host Configuration Protocol (DHCP), and Windows Admin Center (WAC). Figure 10 provides a recap of our test environment architecture displaying the overview of our environment:
The TPROC-C benchmark was used for our tests. This benchmark is an Online Transactional Processing (OLTP) benchmarking standard derived from the TPC-C standard. Our tests included a dataset with 4000 Scale factor. This resulted in a database sized at approximately 400 GB. These tests were initially performed with a single VM and then scaled to measure performance. The testing parameters configured for each HammerDB instance are captured in Table 2.
Parameter | Value |
Number of warehouses | 4000 |
Use all warehouses | False |
Key and Think Time | Enabled |
Asynchronous User Scaling | Enabled with 10 clients/user |
Number of Users | 10, total sessions =100 |
Due to the delay and low transaction load introduced by enabling Keying and Thinking time, we edited the virtual user script to reduce the delay induced to a single second in only the rarest of transactions. Figure 11 shows a snippet of our updated script.
For every VM running SQL Server 2022 to be benchmarked was configured with settings described in Table 3.
Setting | Value |
vCPU | 6 |
Memory for VM | 384 GB |
Memory assigned to SQL Server | 320 GB |
Dynamic Memory | Disabled |
CPU reservation | 100% |
Each of the databases had their files on a dedicated fixed size Resilient File System (ReFS) volume which included two datafiles within the same file group and a log file. Each of these volumes were created as three-way mirrors to maximize performance.
We used Live Optics monitoring with HammerDB execution to capture the details of our testing.