Best Practices for Running SQL Server with Hyper-V Dynamic Memory

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.

Also read: Best Practices for Running SQL Server in Virtual Machines

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.

• Read the Official Documentation from Microsoft here.
• Download the SQL Technical Article here.