Enterprise TechSQL

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.

What's your reaction?

In Love
Not Sure

You may also like

Comments are closed.