Home > Workload Solutions > SQL Server > Best Practices > Intel-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Introduction to Best Practices for SQL Server > Baseline Configuration
This section covers details of the baseline configuration. A baseline configuration was used to determine the initial workload and to provide a comparison for the first set of best practices. After each test the database was refreshed with the best practices retained. This test methodology allowed the team to compare results as best practices were progressively added to the database.
To achieve the goal of 80 percent overall processor utilization in the R740 server, we ran four virtualized databases in parallel. After several tests using HammerDB and an OLTP workload, a baseline configuration was developed. The table below 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 four virtualized databases |
vCPU | 8 cores | 32 cores |
Memory | 160 GB | 640 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 an eight-core reservation. With four total virtualized databases, the total vCPU reservation was 32 cores on the server (four virtual machines each with eight cores). The PowerEdge R740s servers are two-socket servers. In the configuration for our tests, we used two Intel Xeon Gold 6254 CPUs. Each Intel CPU has 18 cores, thus there was a total of 36 physical cores. The total of 32 vCPU across the four virtual machines, resulted in a total of four unallocated physical cores.
While the virtual machines used most of the physical cores by default, the PowerEdge R740 servers enable logical processors. When logical processors are enabled, the hypervisor presents twice as many processor cores. In our configuration, the 36 physical processor cores are presented as 72 cores (36 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 reserved, and 72 cores were available.
There is no best practice to describe how processor cores were allocated to the virtual machine in the baseline configuration. Rather, the target of 80 percent% CPU utilization was the determining factor in guiding the number of vCPUs per virtual database. Testing showed the server reached 80 percent utilization with four virtualized databases and 68 processors when running the HammerDB OLTP workloads.
The memory configuration per virtual machine included a reservation of 160 GB. With a total of four virtual machines, a total of 640 GB of memory was reserved. The total memory available in the server was 1.5 TB, meaning the virtual machines used almost half of the available physical memory in the server. The goal in allocating memory to each virtual machine was to ensure each VM fit into a physical non-uniform memory access (NUMA) node. 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 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 160 GB of memory, thus enabling the use of only the local memory bank. 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.
The PowerMax has the ability to have parent and child storage groups, 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
Parent Storage Group | Child Storage Group | Number of volumes | Total volume(s) size (GB) | Notes |
VM1_DB1_SG | DataLUNSG | 4 | 1,000 | Data files |
| LogLUNSG | 1 | 512 | Log files |
VM1_TEMP_SG | TempDataSG | 1 | 300 | Temp Data files |
| TempLogSG | 1 | 300 | Temp Log files |
A naming convention using DBn allowed the database administrators to increment the value to create storage groups for all the databases. For example, the second copy of the database had a parent storage group name of VM2_DB2_SG with child groups of DataLUNSG and DataLUNSG. Using this naming convention allowed the administration team to repurpose copies to the database quickly.
Table 7 shows the SQL database volume configuration:
Table 7: SQL Database Volume Configuration
Parent Storage Group | Child Storage Group | VMFS Datastore | Volume(s) size (GB) | Notes |
VM1_DB1_SG | DataLUNSG | Data1 | 256 | Total across all 1,000 GB |
|
| Data2 | 256 |
|
|
| Data3 | 256 |
|
|
| Data4 | 256 |
|
| LogLUNSG | Log | 512 |
|
VM1_TEMP_SG | TempDataSG | TempData | 300 |
|
| TempLogSG | TempLog | 300 |
|
Table 7 includes the VMFS Datastore column to show the relationship of the storage groups to the VMware File System storage. Within the DataLUNSG child storage group there were four VMFS datastores: data1 through data4. Each datastore was the same size, 256 GB. This meant that the four totalled 1,000 GB. The other storage groups had a 1-to-1 relationship with VMFS datastores. For example, LogLUNSG had the Log datastore and the TempDataSG had the TempData datastore. The benefit of this storage design is that there are fewer storage groups to manage. The challenge to using this storage approach is that performance monitoring at the PowerMax level will only report metrics at the storage group level.
The SQL Server instance configuration includes many default settings that reflect a new installation of the database. Table 8 includes a subset of the database configuration parameters to show the baseline settings.
Table 8: Instance configuration parameters
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 can be labeled as an entry-level database through a review of the configuration parameters. 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. Starting with a minimal configuration and adding best practices helps customers address database growth challenges using these validated recommendations.