Resources are scarce both in the real world as well as in the virtual world. When you are left with an overburdened virtual machine, system administrators tend to lean towards the idea of using dynamic memory across virtual machines.
But, the real question is, is this a good idea? Does Microsoft recommend running SQL Server in a virtual machine configured with dynamic memory? Read along to find out.
Best Practices for Running SQL Server with Hyper-V Dynamic Memory
Microsoft recommends disabling Dynamic Memory for virtual machines running SQL Server. It applies to both VMware as well as Hyper V. Memory is a critical resource for SQL Server, and any resource crunches can cause an adverse 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-center resources. In this post, I am going to talk about best practices for running SQL Server with Hyper-V Dynamic Memory.
Also read: Best Practices for Running SQL Server in Virtual Machines
Here are the fundamental concepts that need 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 startup 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.