One of the biggest factors that influences SQL Server® performance is memory. This is true whether the SQL Server instance is running on a physical or a virtual server. On a physical server, the easiest path to ensure that you are taking full advantage of SQL Server’s memory requirements is simply to put as much RAM in the server as you can.
However, things are a bit more complicated for virtual SQL Server instances. Typically, you can’t allocate the maximum amount of RAM to every VM that might be running SQL Server. Hyper-V® in Windows Server® 2012 R2 supports a maximum of 1TB of RAM, and up to 64 virtual processors per VM. Using the maximum VM configuration settings limits the number of VMs you can run simultaneously. In addition, over-configuring your VMs would result in the inefficient use of host resources.
Most systems have peak utilization periods during which they need more computing resources. However, they also have other times when the workload drops off and those resources could be released. Over-configuring the VMs would tie up those host resources, preventing them from being used by other VMs.
Hyper-V’s dynamic memory (first introduced in Windows Server 2008 R2 SP1) provides a solution to this problem. The memory works by allocating all the physical RAM in the virtualization host to a shared pool. The memory from the dynamic memory pool is divided between the VMs running on the host. The hypervisor adjusts the amount of memory allocated to each VM based on the requirements of each VM.
If a VM’s workload increases, memory is dynamically added to the VM. If the VM’s workload and memory demand later decreases, or other VMs on the same Hyper-V host have higher-priority memory requests, memory is dynamically removed from the VM.
See this video for a demonstration of Hyper-V Dynamic Memory on SQL Server 2014:
Dynamic Memory Requirements
To use Hyper-V dynamic memory, the Hyper-V host must be running Windows Server 2008 R2 SP1 or later. In addition, the guest OS running in the VM must support the ability to hot-add RAM. The following Windows Server guest OSs can use Hyper-V dynamic memory:
- Windows Server 2012 R2
- Windows Server 2012
- Windows Server 2008 R2 SP1
- Windows Server 2008 SP2
- Windows Server 2003 R2 SP2
In addition to the OS support you need to have one of the following editions of SQL Server:
- SQL Server 2005 Enterprise edition
- SQL Server 2008 or SQL Server 2008 R2 Enterprise edition
- SQL Server 2012 Enterprise edition
- SQL Server 2014 Enterprise edition
Notably, the SQL Server Standard edition does not have the ability to hot-add RAM. Therefore, it cannot take advantage of Hyper-V dynamic memory.
Configuring Hyper-V Dynamic Memory
To enable a VM to use Dynamic Memory, open the Hyper-V Manager, and select the VM you want to configure in the Virtual Machines pane, making sure the VM is powered off. You can’t enable or disable Dynamic Memory if the VM is in either the Running or Saved state. Right-click the VM to bring up the context menu, select Settings, and click the Memory page. This displays the Memory page as shown in figure 1.
To enable Dynamic Memory, check the Enable Dynamic Memory box. Next, you can optionally change the Startup, Minimum, and Maximum RAM settings.
The Startup RAM setting ensures there is enough RAM available to start the VM. This setting should typically be at least a 1GB for Windows Server 2012 and Windows Server 2012 R2.
The Minimum RAM setting can actually be lower than the Startup RAM setting. This setting enables the VM to give up any memory it may not need after the VM has completed the boot process.
The Maximum RAM setting puts a ceiling on the amount of RAM that the VM can dynamically use. The Memory buffer setting controls the amount of RAM that the VM will acquire as it dynamically grows. It is set using a percentage.
In figure 1, you can see that if the VM needs to dynamically grow, it will allocate an additional 20% more RAM than it currently has. The memory weight specifies how important this VM is in comparison to other VMs that are running, which also use Dynamic Memory.
Setting the memory weight to high causes Hyper-V to put a priority on this VM, and it will not surrender memory to lower priority VMs.
There are a couple of things you need to know when using SQL Server with Hyper-V Dynamic Memory. First, when SQL Server starts, it computes the size of virtual address space for the buffer pool. Because the size of virtual address space is computed at startup, it is static and does not grow in response to memory being added to the system.
When SQL Server starts, if it detects that Hot Add Memory is supported, it sets the reserved size for the virtual address space of the buffer pool to 16 times the value of Startup Memory. Next, if you use SQL Server with Hyper-V Dynamic Memory, Microsoft® recommends using the Locked Page Memory model. This ensures that SQL Server memory is never paged out, and that it’s also aware of dynamically changing memory in the system.
This setting can help prevent Dynamic Memory from attempting to remove memory from SQL Server VMs. You can see how to set the Lock Page Memory model at Enable the Lock Pages in Memory Option.
In the whitepaper Running SQL Server with Hyper-V Dynamic Memory Best Practices and Considerations, Microsoft performed several performance tests comparing the usage of Dynamic Memory with static memory. In one set of tests they ran a sample workload on 8 VMs configured with 7.5GB of static memory, and again on 8 VMs configured to use Dynamic Memory with 2GB of startup memory, and 12GB of maximum memory.
The workload ran at a constant 12,500 SQL batches per second. In the static memory scenario, the systems required 12,000 sustained I/O operations per second (IOPS). In the dynamic memory scenario, the systems averaged 7,500 IOPS. The Dynamic Memory configuration supported the same throughput, but required only 60% of the total IOPS—thanks to a larger buffer pool.