Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Introduction to Best Practices for SQL Server > Baseline Configuration
In this section we cover details of the baseline configuration. A baseline configuration was used for determining the initial workload and to enable a comparison with the first set of best practices.
To achieve the goal of 80 percent overall processor utilization in the PowerEdge R7525 server, four virtualized databases were run in parallel. After several tests using HammerDB and an OLTP workload a baseline configuration was developed. The following table shows the vCPU and memory allocation for each virtualized database:
Table 5: vCPU and memory allocation
Resource allocation | Baseline configuration per virtual machine | Total across four virtualized databases |
vCPU | 8 cores | 32 cores |
Memory | 128 GB | 512 GB |
The virtual machines were allocated 8 virtual cores and 128 GB of memory. With four total virtualized databases the total vCPU was 32-cores (4 virtual machines each with 8 virtual cores) on the server. The PowerEdge R7525 servers are two socket servers and in the configuration for our tests two AMD EPYC 74F3 processors were used. Each AMD CPU has 24 cores thus, there was a total of 48 physical cores. The total of 32 vCPU across the four virtual machines leaves a total of 16 unallocated physical cores.
While the virtual machines used most of the physical cores by default, the PowerEdge R7525 servers enable logical processors. When logical processors are enabled, the hypervisor presents twice as many processor cores. In our configuration, the 24 physical processor cores will be presented as 48 cores (24 x 2). Logical processors can boost performance by enabling more executions in parallel. In the case of our four virtual databases, we used nearly half of the available processor resources: 32 processor cores were allocated, and 96 cores were available.
The memory configuration per virtual machine included an allocation of 128 GB. With a total of four virtual machines, 512 GB of memory was allocated. The total memory available in the server was 1 TB. This indicates that the virtual machines used half of the available physical memory in the server. In allocating memory to each virtual machine, the goal was to ensure each VM fit into a physical NUMA node and will not be NUMA-wide. Non-uniform memory access NUMA refers to 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 remote memory bank. When a virtual machine’s memory spans two memory banks, wait times are incurred causing sub-optimal performance. VMware’s Architecting Microsoft SQL Server on VMware vSphere Best Practices Guide has more information on NUMA considerations.
To prevent this sub-optimal NUMA design, each virtual machine has been allocated 128 GB of memory thus, enabling the use of only the local memory bank. The VMware ESXi hypervisor is responsible for management of memory on a processor and has been designed to optimize performance and if possible prevent NUMA-wide configurations. By allocating memory that enables the virtual machine to use a local memory bank we have optimized performance for all the virtual machines on the server.
The PowerMax features the ability to have parent and child storage groups that enables the administrator to create a configuration that facilitates ease of management, efficient snapshots, and replication. For the baseline storage configuration, all four virtualized databases used the same design. Table 6 shows the configuration detail for the first virtualized SQL Server database:
Table 6: Configuration of first virtualized SQL Server database
Parent storage group | Child storage group | Number of LUNs | VMware volumes | Volume size (GB) | Notes | |
ParsleyDB1 |
| 1 | Parsley_DB1 | 750 | All database files |
A naming convention was used to enable fast clones that can be repurposed to testing:
This baseline storage configuration provides for easy management of the SQL Server database, however, is not the optimal design for performance and snapshots. In the storage best practice, we present an optimized storage design that will improve performance and enable improved snapshots. This is an example of how best practices can increase performance and enable better database repurposing as part of the overall goal of optimizing SQL Server databases.
The SQL Server database configuration includes many default settings that reflect a new installation of the database. Table 8 lists a subset of the database configuration parameters to show the baseline settings:
Table 8: Database baseline configurations
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 |
In reviewing the configuration parameters, the overall profile can be labeled as an entry level database. An entry level database configuration provides a foundation to build upon best practices. Over the course of validating best practices the database configuration will change to optimize performance. This approach of starting with a minimal configuration and adding best practices has the benefit of enabling customers to address database growth challenges using these validated recommendations.