Using Hyper-V Dynamic Memory with SQL Server

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 virtual server. On a physical server, the easiest path to ensure that you don't have any memory bottlenecks is to simply put as much RAM in the server as you can. However, things aren't that simple for a virtual SQL Server instance. Sure, you can allocate the maximum amount of RAM to the virtual machine (VM), which is 16GB for Hyper-V 2.0. However, using the maximum RAM setting for the VM would limit the number of other VMs you could run simultaneously because RAM is a limiting factor to VM density. In addition, this practice would likely result in inefficient use of resources. Most systems have peak utilization periods, during which they need all the RAM they can get. But they also typically have many periods in which the workload drops off. Over-configuring the RAM would tie up that vital resource and prevent it from being used by other VMs when they might need additional resources.

Hyper-V's ability to dynamically allocate and deallocate memory to running VMs addresses this problem head-on. Introduced as a part of Windows Server 2008 R2 SP1, Hyper-V dynamic memory changes the way that the Hyper-V hypervisor allocates memory. Instead of allocating a fixed amount of RAM to each VM, dynamic memory enables RAM to be seen as a shared resource that can be dynamically and automatically divided between the running VMs. With dynamic memory enabled, Hyper-V can dynamically provide a VM with more or less memory in response to changes in the workload of the running VMs. This allows memory resources to be distributed more efficiently between active VMs, giving VMs better performance when they need it as well as enabling you to achieve greater VM density on the same physical host.

Related:How to Create a Linux Virtual Machine in Hyper-V

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 guest OSs can utilize Hyper-V dynamic memory:

In each case, you need to be sure the Windows Server 2008 R2 SP1 Hyper-V Integration Services components are installed in the guest OS.

How Dynamic Memory Works

Dynamic memory works by allocating all the physical RAM in the virtualization host to a shared pool minus the host reserve RAM that's set aside for the parent partition. The actual amount of RAM reserved for the host varies according to the amount of physical RAM in the virtualization host. You can calculate the amount of host reserve RAM using the formula:

Related:Migrating From VMware: Guide to a Successful Transition

Host Reserve = 384MB + 30MB per GB memory of the host

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 it. If the VM's workload later decreases or other VMs on the same Hyper-V host have higher-priority memory requests, memory is dynamically removed from the VM.

In a production server consolidation environment, different VMs have different workload priorities. Hyper-V dynamic memory lets you assign a different memory priority value to each VM. VMs with higher performance requirements should be assigned a higher memory priority than VMs that have less-critical requirements. Memory priority is used when all the available physical memory in the Hyper-V host has been allocated to the running VMs. It's important to remember that when you increase a VM's memory priority and all the memory in the host is allocated, the amount of memory allocated to VMs with a lower memory priority will decrease.

Related:How To Copy a Hyper-V Virtual Machine to a New Location

Hyper-V dynamic memory uses a technology called ballooning to release memory. Ballooning works by having the VM communicate with the host to specify that certain memory pages won't be accessible by the guest OS, reducing the amount of memory available to the guest. When the VM requests additional memory from the host, the memory that was previously ballooned will be released as needed until all the balloon memory has been returned to the guest OS. If the guest continues to request additional memory, the Hyper-V host will provide the memory from its memory pool until the VM reaches its memory threshold or until another VM with a higher memory priority requests the host memory.

Hyper-V determines the amount of memory a VM needs using a metric called memory pressure. Hyper-V measures memory pressure by first determining the total committed memory of the guest OS running in the VM, then calculating the ratio of how much memory the VM wants versus how much memory it has.

The amount of memory that Hyper-V assigns to the VM equals the total committed memory plus some additional buffer memory. The formula is:

VM Memory = Memory Demand +
Memory Demand ´ Memory Buffer Percentage

The amount of buffer memory is configurable on a per-VM basis as a percentage of committed memory. Configuring a memory buffer of 50 percent means up to 50 percent of the guest's committed memory will be allocated to the VM.

Dynamic Memory Configuration

To enable a VM to use dynamic memory, open Hyper-V Manager and select the VM you want to configure in the Virtual Machines pane. Make sure the VM is powered off. You can't reconfigure a VM's RAM if it's in either the Running or Saved state. Right-click the VM to bring up the context menu, select Settings, and click the Memory page. You can see the Memory page in Figure 1.

Figure 1: Configuring Hyper-V dynamic memory 

In the Memory management section of the Memory page, click the Dynamic radio button. Notice the Startup RAM and Maximum RAM fields. Instead of specifying a static amount of RAM to be allocated to a VM, Hyper-V dynamic memory lets you specify a minimum amount of RAM that will be used when the VM starts and a maximum of amount of RAM that the VM can allocate. The Startup RAM value is the lower limit of the memory that the VM will use. In this case, 512MB is the minimum required to boot Windows Server 2008. The Maximum RAM value is the upper limit of how much memory the VM can allocate. In Figure 1, you can see the Maximum RAM has been capped at 3,596MB. For Hyper-V 2.0 (the version included with Windows Server 2008 R2 SP1), the maximum value for this setting is 65,536MB, or 64GB. It's important to understand that after the VM is running and has been processing workloads, its memory allocation will be somewhere between these two values.

The Memory buffer setting controls the amount of memory Hyper-V will reserve in addition to the memory demand for a VM. You can see how the startup memory, maximum memory, and memory buffer work together in Figure 2.

Figure 2: Understanding how the startup memory, maximum memory, and memory buffer work together 

In the example shown in Figure 1, the buffer is set to 20 percent so Hyper-V will use the following calculation to determine how much memory to allocate to the first memory request:

20% / (100% - 20%) ´ 512MB = 128MB

This formula changes as the committed memory increases.

At the bottom of Figure 1, you can see the Memory weight setting. Hyper-V uses this value to determine which VMs to allocate memory to after all the pooled memory in the system has been allocated. Setting the memory weight to a high value will cause Hyper-V to give this system memory priority over systems that have a lower setting. Again, remember that this setting only comes into play once all the host memory has been committed.

After the desired dynamic memory settings are configured, clicking Apply commits the changes to the VM. The dynamic memory settings kick in immediately after the VM is restarted.

Note that using dynamic memory isn't an all-or-nothing scenario. You can freely mix VMs that use dynamic memory with VMs that use static memory on the same Hyper-V host.

SQL Server and Dynamic Memory

Although it might seem a bit obvious, it's worth pointing out that Microsoft absolutely intends for Hyper-V dynamic memory to be used in production environments. Microsoft doesn't always put its weight behind all of the technologies it offers. For instance, some technologies such as NIC teaming in Windows Server 2008 R2 are available, but if you call Microsoft Customer Service and Support (CSS) with a problem, CSS's first advice is to turn it off. That's not the case with Hyper-V dynamic memory. Microsoft fully supports Hyper-V dynamic memory, and it's definitely designed for production use.

Virtualized SQL Server instances can benefit greatly from this Hyper-V feature because memory is one of the most important factors influencing SQL Server performance. To take advantage of Hyper-V dynamic memory, you need to be using the Enterprise edition of SQL Server 2008 or later, or the Datacenter edition of SQL Server 2008 R2 or SQL Server 2008. The Enterprise and Datacenter editions are required in order to have SQL Server's hot-add RAM capability. Other editions of SQL Server don't have the ability to utilize hot-add RAM and can't take advantage of dynamic memory. All the other editions of SQL Server should use Hyper-V's static memory setting.

When new memory is allocated to a guest VM by Hyper-V dynamic memory, it looks like hot-add physical memory to the SQL Server instance. SQL Server Enterprise and Datacenter can dynamically grow if more memory is added to the guest VM.

When SQL Server processes workloads, the sqlservr.exe process allocates and commits memory from the OS. SQL Server dynamic growth requires three factors:

The default setting for max server memory is 2,147,483,647MB. When a SQL Server workload causes the sqlserver.exe process to grow, the Hyper-V memory scheduler will detect memory pressure in the guest VM and dynamically add memory to it. SQL Server will detect the added memory and grow to meet the workload demand. SQL Server checks OS memory every second and dynamically adjusts its memory according to the available memory and the max server memory setting.

In the white paper "Running SQL Server with Hyper-V Dynamic Memory," Microsoft conducted a set of workload tests that compared eight SQL Server VMs. In one set of tests, it ran a sample workload on eight VMs configured with 7.5GB of static memory and on eight VMs configured for dynamic memory. The dynamic memory settings were 2GB of startup memory and 12GB of maximum memory. The workload ran at 12,500 SQL batches per second. In the dynamic memory scenario, the systems averaged 7,500 sustained I/O operations per second (IOPS). In the static memory scenario, the systems required 12,000 sustained IOPS. So, dynamic memory supported the same throughput with only about 60 percent of the total IOPS.

In Figure 3, you can see the results of the dynamic memory test in Performance Monitor. The solid black line shows the SQL Server buffer pool memory. The green line shows the number of disk reads per second. In the beginning of the test run, you can see that the number of disk reads was very high, but as additional memory was allocated to the VM, SQL Server was able to increase the size of its buffer pool. As a result, the number of disk reads dropped by nearly 50 percent.

Figure 3: Monitoring performance during the dynamic memory test 

SQL Server doesn't like to give up memory once it has allocated it. However, SQL Server will shrink its caches in response to memory pressure when the OS sends low-memory signals. The SQL Server Resource Monitor checks for low-memory signals about every five seconds, and it will attempt to free memory until the low-memory signals stop. SQL Server will also release memory when the size of its caches and allocations increase, but the SQL Server process needs to keep its total memory within the value of the max server memory setting. Decreasing the value of the max server memory setting can create internal memory pressure resulting in SQL Server releasing memory back to the OS.

If pressure from other VMs causes Hyper-V dynamic memory to take memory away from a VM, Windows Memory Manager will page out unlocked portions of memory and raise low-memory notification events. When SQL Server detects these events, it'll shrink memory until the low-memory notifications stop. To reduce memory usage, SQL Server will free any used memory, free unreferenced cache blocks, and delete items in the buffer pool based on the oldest access times.

As VM guest memory decreases, it's possible that the guest OS can page out parts of the SQL Server working set, which has a very negative impact on SQL Server performance. To prevent this from happening, Microsoft recommends using the Locked Page Memory Model in conjunction with Hyper-V dynamic memory. Using this model ensures that SQL Server memory is never paged out. You can see whether SQL Server is using it by running the query

EXEC ('xp_readerrorlog 0, 1, ''Using locked pages''') 

You can enable the Locked Page Memory Model for SQL Server using the Windows Group Policy tool.

Making this change requires systems administrator permissions. To enable the Locked Page Memory Model, perform the following steps on the guest VM:

1. Open the Start menu and select Run. Type gpedit.msc in the Open box and click OK to open the Group Policy console.

2. In the Group Policy console, expand Computer Configuration, then Windows Settings.

3. Expand Security Settings, then Local Policies.

4. Select the User Rights Assignment folder. The policies will be displayed in the details pane.

5. Double-click Lock pages in memory.

6. Click Add in the Local Security Policy Setting dialog box.

7. In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

Dynamic Memory Monitoring

You can use Hyper-V Manager to monitor dynamic memory usage. Hyper-V Manager tracks the assigned memory, memory demand, and memory status for all VMs. You can see these metrics displayed in Figure 4.

Figure 4: Using Hyper-V Manager to monitor dynamic memory usage 

In addition, you can use Windows Performance Monitor counters to monitor dynamic memory usage. In the Hyper-V virtualization host, you can monitor the following counters:

Expand Your RAM

If you're running virtual SQL Server instances in a server consolidation environment and you have the Enterprise edition of SQL Server 2008 or later, or the Datacenter edition of SQL Server 2008 R2 or SQL Server 2008, you should definitely look into using Hyper-V dynamic memory. For more information about using it, see the "Hyper-V Dynamic Memory Configuration Guide." You might also want to check out the four-part blog " SQL Server and Hyper-V Dynamic Memory" on MSDN. There's also a great white paper " Running SQL Server with Hyper-V Dynamic Memory" that provides a lot of detailed information about the interaction of SQL Server and dynamic memory, along with a set of best practices.