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.
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.
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)
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.
Using this option, you can pre-allocate memory to SQL Server.
At instance startup and force the “Memory Demand” to meet the usage requirements.
Once this option is selected, Windows will not be able to page-out the memory assigned to SQL Server.
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.
Comments are closed.