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 decrease, or other VMs on the same Hyper-V host have higher-priority memory requests, memory is dynamically removed from the VM.
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.
I’ve read the whitepapers and the online articles but in the real world admins see the SQL server take all the memory up to the (SQL) max server memory setting and then never let it go, so what is the point of dynamic memory with SQL really? You might as well just set it to static to match the max server mem setting.
Quote: “A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory”
So there needs to be a shortage of free memory in a SQL VM which means there needs to be a shortage on the host, which means you need to be running at max capacity on your host, which is obviously a bad thing to do. But, say you were running at this capacity, why would you want the memory released from your SQL VMs? You wouldn’t, you want them to keep their memory so you give them higher memory priority in the VM settings, the upshot of which is that other VMs release their memory first so again, your SQL VM effectively has static memory.
Given the anecdotal evidence from admins and DBAs the world over that DM for SQL acutally causes problems and the slim scenarios that it’s actualyl any use, what exactly is the point?
I never understood that MS performance test where they compare 7.5GB static VMs with 12GB dynamic ones and marvel at the performance increase. If there was RAM available for them to run at 12GB you might as well give them 12GB static to start with because you aint getting it back unless your host is about to fall over!