For SQL Server 2017, VM memory can be allocated either dynamically or statically. To decide between dynamic and static memory allocation, 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 NUMA node on the host’s physical architecture
- Preference between performance and levels of scalability
In general, choose dynamic memory allocation when VMs are unmonitored and are relatively small, and when scalability is preferred over performance. For larger production VMs that are reasonably monitored, choose static memory allocation for better and more consistent performance.
Dynamic memory allocation
For more information about dynamic memory allocation, see Running SQL Server with Hyper-V Dynamic Memory in the Microsoft Developer Network (MSDN) library.
Consider the following recommendations for dynamic memory:
- Determine startup RAM and minimum memory values based on your needs. Microsoft recommends leaving the max server memory value at its default setting, which allows SQL Server to manage memory dynamically. However, Dell EMC recommends that you change this value, as needed, if one or both of the following apply:
- 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 (default value is zero) based on usage and performance considerations because dynamic memory is enabled for the VM.
- Set 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 memory required for any other applications and the operating system.
Static memory allocation
Consider the following recommendations for static memory allocation:
- To maximize performance, assign memory to a VM based on its virtual and physical NUMA architecture.
- To ensure optimal performance, disable the NUMA Spanning option for the Hyper-V host so that each virtual NUMA node is backed by a single NUMA node.
Note: Use the NUMA Spanning option with caution. Hyper-V will not start, restore, or accept a live migration for a VM if the hypervisor is unable to map each virtual NUMA node to a physical NUMA node.