Home > Workload Solutions > SQL Server > Guides > Design Guide—Modernize Your Microsoft SQL Server Platform and Accelerate Deployments > Baseline configuration
A baseline configuration determined the initial workload and enabled comparison with the first set of best practices.
We developed a baseline configuration for this solution, that included deployment parameters for the entire stack. This included the amount of vCPU and memory for each of the VMs, which we determined from previous experiences with similar setups and refined by performing additional performance tests and evaluations. We performed this configuration using a HammerDB benchmarking tool simulating an OLTP workload of 10,000 warehouses (approximately 1 TB in size) for each database per virtual machine.
For the final baseline configuration, two virtualized databases were run in parallel. The following table shows the vCPU and memory allocation for each virtual machine:
Resource reservation | Baseline configuration per virtual machine | Total across two virtualized databases |
vCPU | 6 cores | 12 cores |
Memory | 384 GB | 768 GB |
The virtual machines included 6 virtual cores and 384 GB of memory. With two virtualized databases, the total vCPU was 12 cores on the server. The PowerEdge R750 servers are two socket servers. For these tests, two Intel Xeon Gold 6334 processors were used. As each Intel processor has eight cores, these tests included a total of 16 physical cores.
Although the virtual machines used most of the physical cores by default, PowerEdge R750 servers enable logical processors. When logical processors are enabled, the hypervisor presents twice as many processor cores. Logical processors can boost performance by enabling more performance in parallel. In the case of our two virtual databases, we assigned less than half of the available processor resources; we allocated 12 vCPUs and vCPU were available.
For the two virtual machines, 768 GB of memory was allocated (384 GB each). The total memory available in the server was 1 TB. This indicates that the virtual machines used less than half of the available physical memory in the server. In allocating memory to each virtual machine, the goal was to ensure that each virtual machine fit into a physical non-uniform memory access (NUMA) node and would not be NUMA-wide. 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 data retrieval from a remote memory bank. When a virtual machine’s memory spans two memory banks it is called a NUMA-wide configuration and wait times increase and cause suboptimal performance. See VMware’s Architecting Microsoft SQL Server on VMware vSphere Best Practices Guide for more information about NUMA considerations.
The VMware ESXi hypervisor is responsible for the management of memory and has been designed to optimize performance and if possible, prevent NUMA-wide configurations. Allocating 384 GB of memory to each virtual machine optimized performance by enabling the use of the local memory bank only.
PowerStore features the ability to create Volume Groups that enable administrators to create a configuration that facilitates ease of management, efficient snapshots, and replication. For the baseline storage configuration, both virtualized databases used the same design. Table 5 shows the configuration detail for the first virtualized SQL Server database:
Volume group | Volumes | VMware volumes | Volume size (TB) | Notes | |
Tarragon DBs | TarragonDB1
| VM1_Data
| 1.5 | All database files | |
| Tarragon DB2 | VM2_Data | 1.5 |
|
Using this approach, the engineering team could create copies of the SQL Server database quickly and easily. For this reason, they selected this configuration for the default baseline configuration.
The SQL Server database configuration includes many default settings that reflect a new installation of the database. Table 6 lists a subset of the database configuration parameters to show the baseline settings.
Parameters | Values |
Maximum Degree of Parallelism (MAXDOP) | 0 |
Cost Threshold of Parallelism | 5 |
Recovery mode | Full |
Query Optimizer fixes | OFF |
Max worker threads | 0 |
Min server memory | 0 MB |
Max server memory | 2147483647 MB |
The overall profile is the baseline database configuration. This configuration provided a foundation for the best practice tests.