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

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

Best Practices for Running SQL Server with Hyper-V Dynamic 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.
Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.