Consider the following best practices for configuring the Windows Server operating system:
- Use an allocation unit size of 64 KB to format the volume that stores the database files.
- Enable Windows Lock Pages in Memory (LPIM) policy by using a domain user account for the SQL Server service identity. The LPIM policy determines which accounts can use a process to keep data in physical memory. This prevents the Windows operating system from paging out a significant amount of data from physical memory to virtual memory on disk.
Consider the following best practices for configuring the Hyper-V host and deploying VMs:
- Do not over commit resources. Hyper-V allows overcommitting resources such as CPU and network. However, overcommitting leads to performance degradation when the resource usage exceeds the available resources.
- Provision either static or dynamic memory, depending on the workload in consideration. For more information, see Memory settings.
- Provision compute resources to optimize use. A vCPU is a representation of the physical core of a processor, or threads or logical processors in the core. In Hyper‑V, you can adjust the relative weights and reserves for a VM by going to Settings > Processor. If CPU resources are overcommitted, setting the weights and reserves optimizes the way these resources are used. You can prioritize or deprioritize VMs based on your needs.
- Use fixed-size virtual hard disks (VHD/VHDX) for production workloads. Using dynamic virtual hard disks can result in occasional pauses when the dynamic disks have to be resized. Use dynamic disks for noncritical test environments or nonproduction environments.
- Use separate VHDs/VHDXs for operating system, SQL Server data files, and SQL Server log files to rule out disk contention and ensure the best performance.