Best practices for configuring the Windows operating system include:
- Use an allocation unit size of 64 KB to format the volume that stores the database files.
- Enable the Windows Lock Pages in Memory (LPIM) policy by adding an account with privileges to run sqlservr.exe. The LPIM policy determines which accounts can use a process to keep data in physical memory. The policy prevents the Windows operating system from paging out a significant amount of data from physical memory to virtual memory on disk.
- Configure the High Performance power profile.
Best practices for configuring ESXi and deploying VMs include:
- Do not overcommit resources. ESXi allows overcommitting of resources such as CPU, memory, and network; however, overcommitting leads to performance degradation when the resource usage exceeds the available resources.
- Calculate host memory as follows:
HostMem >= Sum of VM memory + overhead
- Set the memory reservation equal to the provisioned memory to eliminate the possibility of ballooning or swapping. When calculating the amount of memory to provision for the VM, use the following formulas:
- VM Memory = SQL maximum server memory + ThreadStack + operating system memory + VM overhead
- ThreadStack = SQL maximum worker threads x ThreadStackSize
- ThreadStackSize = 1 MB on x86 = 2 MB on x64 OS Mem: 1 GB for every 4 CPU cores
- Provision compute resources according to your workload requirements. Ensure that the CPU utilization at the VM level is less than 75 percent. A virtual CPU (vCPU) is a representation of the physical core of a processor or threads (logical processors) in the core.
- Use fixed-size virtual hard disks for the operating system. Dell EMC recommends using fixed virtual hard disks (VHD/VHDX) for production workloads. Using dynamic virtual hard disks can result in occasional pauses during disk resizing. Use dynamic disks for noncritical test environments or nonproduction environments.
- Use the High-Performance power management profile. To change power profiles, see Host Power Management Policies.
- Use separate storage LUNs for data and log files to rule out disk contention. For best performance, create distinct LUNs for SQL Server data files and SQL Server log files. These strategies also help to create different access patterns on the storage system—sequential access for log files and random access for data files. The access patterns enable the Unity 650F array to manage the data most effectively.
- Use Raw Device Mapping (RDM) for LUNs. For more information about the benefits of RDM and how to configure it, see About Raw Device Mapping.
- Use VMware Para-Virtual SCSI (PVSCSI) adapters. For information about how to map LUNs using PVSCSI adapters, see VMware KB 1010398, Configuring disks to use VMware Paravirtual SCSI (PVSCSI) adapters.
- Adjust the Disk.reqCallThreshold value for your VMs. For instance, for OLTP, we found that the lowest latency occurred at the VM level with the parameter set to 1. For decision support, we achieved the best results with the default value of 8. For more information, see Virtualizing Performance—Critical Database Applications in VMware vSphere 6.0 Performance Study.
- Adjust the VM latency setting value from normal to high.
This section provides guidelines and best practices to optimize a virtualized SQL Server environment.
For SQL Server 2017, you can assign memory for a VM either dynamically or statically. To choose between dynamic and static memory, consider the following factors:
- How frequently you monitor your database
- The VM size (size of the processors, memory, and other resources) as compared to the size of a single non-uniform memory access (NUMA) node on the host’s physical architecture
- Preference between performance and levels of scalability
In general, choose dynamic memory when VMs are unmonitored and are relatively small, and when scalability is preferred over performance. For better and more consistent performance for larger production VMs that are reasonably monitored, choose static memory.
Best practices for dynamic memory include:
- Determine and implement the VM startup RAM and minimum memory based on your needs. Microsoft recommends leaving max server memory at its default setting, which enables SQL Server to manage memory dynamically. However, Dell EMC recommends that you change this value, as needed, if one or both of the following are true:
- You are running multiple applications on the VM.
- You can reasonably ascertain the maximum amount of memory that you want to assign to SQL Server.
- Set min server memory based on usage and performance considerations because dynamic memory is enabled for the VM. The default value for min server memory is 0.
- When using dynamic memory, set the reserves for the VM by using the Memory Buffer option. Base the amount of reserved memory on the min server memory setting for SQL Server and the memory that is required for any other applications and the operating system.
Recommendations for static memory allocation include:
- To maximize performance, assign a particular amount of memory to a VM based on its virtual and physical NUMA architecture.
- Set max server memory and min server memory values based on the amount of memory you want to reserve for the operating system, typical requirements of your SQL Server, and other performance considerations. For more information about memory settings, see Blitz Result: Memory Dangerously Low or Max Memory Too High.
For SQL Server parallelism settings, consider the following recommendations.
Caution: Changing the parallelism settings can have extreme unanticipated consequences. Dell EMC recommends that you do not change these settings without testing and that you are prepared to closely monitor the system in production after making changes.
- The SQL Server configuration option max degree of parallelism controls the number of processors that are used for the parallel execution of a query. If the SQL Server VM in question migrates from hosts with different core configurations, leave this setting at 0; however, if that is not the case, use another value.
- The SQL Server configuration option cost threshold for parallelism specifies a threshold at which query plans run in parallel. You can change the setting for this option from the default value of 5 to 50. You can adjust the setting further, depending on your requirements and testing.
For more information about parallelism settings, see MAXDOP of Confusion.
For more information about Max Worker Threads, see Configure the max worker threads Server Configuration Option.