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.
Note: If you buy something from our links, we might earn a commission. See our affiliate disclosure statement.
Also read: Best Practices for Running SQL Server in Virtual Machines
Here are the key concepts that needs 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 start-up 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.