Home > Workload Solutions > SQL Server > White Papers > SQL Server 2019 Best Practices on PowerEdge Servers and PowerStore T Model Arrays > CPU reservation
To achieve a meaningful test result for a single R750xs server, we ran six virtualized databases in parallel. After several tests using HammerDB and an OLTP workload, we developed a baseline configuration. The following table shows the vCPU and memory allocation for each virtualized database:
Table 5. vCPU and memory allocation
Resource reservation | Baseline configuration per virtual machine | Total across six virtualized databases |
vCPU | 10 cores | 60 cores |
Memory | 112 GB | 672 GB |
A VMware reservation is a guaranteed allocation of CPU or memory for the virtual machine. In the case of vCPU, each virtual machine received a ten-core reservation. With six total virtualized databases, the total vCPU reservation was 60 cores on the server (six virtual machines each with ten cores). The PowerEdge R750xs servers are two-socket servers. In the configuration for our tests, we used two Intel® Xeon® Gold 6338 CPUs. Each Intel® CPU has 32 cores, for 64 total physical cores. The total of 60 vCPU across the six virtual machines, resulted in a total of four unallocated physical cores which can be consumed by the hypervisor.
While the virtual machines used most of the physical cores by default, the PowerEdge R750xs servers enable logical processors. When logical processors are enabled, the hypervisor presents twice as many processor cores. In our configuration, the 64 physical processor cores are presented as 128 cores (64 x 2). Logical processors can boost performance by enabling more executions in parallel. In the case of our six virtual databases, we used nearly half of the available processor resources: 60 processor cores were reserved, and 128 cores were available.
The memory configuration per virtual machine included a reservation of 112 GB. A total of 672 GB of memory was reserved using six virtual machines. The total memory available in the server was 1 TB, indicating that the virtual machines used almost three-fourths of the available physical memory in the server. The goal in allocating memory to each virtual machine was to ensure that each VM fit into a physical non-uniform memory access (NUMA) node. NUMA is a multiprocessor configuration in which each physical CPU has a dedicated memory bank. Information can be retrieved faster from the local memory bank than having to pull the data from another memory bank. When a virtual machine’s memory spans two memory banks, wait times are incurred, causing sub-optimal performance.
To prevent a sub-optimal NUMA design, each virtual machine was allocated 112 GB memory. This memory allocation enabled the use of the local memory bank only. The VMware ESXi hypervisor is responsible for the placement of memory on a processor and has been designed to optimize performance. By reserving memory that enables the virtual machine to use a local memory bank we optimized performance for all the virtual machines on the server.
Each ESXi host (R750xs server) has two dual-port Fibre Channel HBAs. The PowerStore 5000T under test has two Fibre Channel front-end I/O modules (one per node). Each I/O module has four 32Gbps ports with a total of eight 32Gbps front-end ports. There are two Connectrix Fibre Channel switches. Each Connectrix switch is its own fabric. For connectivity, each ESXi host was zoned to all eight front-end ports of the PowerStore across the two switches. This configuration should be the default setup. Figure 2 below shows the SAN zoning configuration.
Figure 2. SAN Zoning Configuration
The PowerStore has the volume group feature, which enables the administrator to create a configuration that facilitates ease of management, efficient snapshots, and replication. For the baseline storage configuration, all six virtualized databases used the same design. Table 6 shows the configuration detail for the first virtualized SQL Server database.
Table 6. Virtualized SQL Server database configuration
Volume Group | Volume Name | Number of LUNs | VMware Datastore | Volume size (GB) | Notes |
sql_vm1_os | sql-vm1-os | 1 | sql-vm1-os-ds | 300 | Operating System |
sql_vm1_db1 | sql-vm1-db1-data1 | 1 | sql-vm1-db1-data1-ds | 512 | Data |
sql-vm1-db1-log1 | 1 | sql-vm1-db1-log1-ds | 256 | Log | |
sql_vm1_db1_temp | sql-vm1-db1-temp-data1 | 1 | sql-vm1-db1-temp-data1-ds | 256 | TempData |
sql-vm1-db1-temp-log1 | 1 | sql-vm1-db1-temp-log1-ds | 128 | TempLog | |
sql_vm1_db1_backup | sql-vm1-db1-backup1 | 1 | sql-vm1-db1-backup1-ds | 1000 | Backup |
A naming convention using VMn and DBn allowed the database administrators to increment the value to create volume groups for all the databases. For example, the second copy of the database had a volume group name of SQL_VM2_DB2. Using this naming convention allowed the administration team to repurpose copies to the database quickly.
Note: Each LUN was configured as a VMware datastore.
For more information about PowerStore best practices for SQL Server, see Microsoft SQL Server Best Practices on PowerStore.