Best Practices for Running SQL Server with Hyper-V Dynamic Memory
Microsoft recommends to disable Dynamic Memory for virtual machines running SQL Server. This is applicable to both VMware as well as Hyper V. Memory is a critical resource for SQL Server and any resource crunches can cause a negative impact to SQL Server workloads. In short, any shared resources is a strict No, be it CPU, Memory, Disks or Networks. However, in some scenarios customers might run SQL Server with Dynamic Memory enabled to save costs and efficiently manage data-centre resources. In this post, I am going to talk about best practices for running SQL Server with Hyper-V Dynamic Memory.
Here are the key concepts that needs to be understood.
- Start-up Memory: The memory assigned to the Virtual Machine when it starts up.
- Memory Demand: Hyper V Dynamic memory estimated how much memory is required by SQL Server or any other Applications.
- Memory Buffer: This is the memory assigned on top of “Memory Demand” to meet any immediate system memory requirements.
- Maximum Memory: The maximum memory that can be allocated to the Virtual Machine.
- Host Memory Reserve: The memory reserved by the Host OS to effectually run its resources including Hyper V and other server roles.
Minimum memory Reserve calculated as follows:
Host Reserve = 384 MB + 30 MB per GB memory of the host
If your start-up memory is 1024 mb, then the maximum buffer pool size of SQL Server will be 16 GB (16000 mb)
How Hyper V does Dynamic memory assignment
Virtual Machine Memory = Memory Demand + (Memory Demand * Memory Buffer Percentage)
The Technique of Adding memory dynamically to Hyper V Guest OS’s is similar to the “Hot Add CPU” feature.
Use Maximum and Minimum Memory
Using this option you can pre-allocate memory to SQL Server at instance start-up and force the “Memory Demand” to meet the usage requirements.
Use Lock Pages in Memory
Once this option is selected, Windows will not be able to page-out the memory assigned to SQL Server.
Start-up Memory is Key
You need to assign at-least 2 GB of initial Start-up memory and Maximum memory of 12 GB can give you better throughputs in shared memory scenarios.