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.
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:
- Windows Server 2008 R2 SP1
- Windows Server 2008 SP2
- Windows Server 2003 R2 SP2
- Windows 7 SP1
- Windows Vista with SP1
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:
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.
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:
- A workload
- A max server memory setting higher than the current memory allocation
- High-memory signals from the OS
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:
- Hyper-V Dynamic Memory Manager VM: Guest Visible Physical Memory. This counter tells you the amount of memory visible in the VM.
- Hyper-V Dynamic Memory Balancer: Available Memory. This counter specifies the amount of memory left on the node that can be assigned to VMs.
- Within the guest OS, you can use the following performance counters to monitor SQL Server's memory usage:
- Process: Working Set. This counter tells you the actual amount of physical memory being used by the SQL Server process in the VM. This isn't valid if the Locked Page Memory Model is used.
- SQL Server: Memory Manager: Target Server Memory. This counter indicates the current target memory of the SQL Server buffer pool manger. Changes in this value are an indication that SQL Server is reacting to memory notifications being raised by Windows.
- SQL Server: Memory Manager: Total Server Memory. This counter lets you know the current size of the SQL Server buffer pool.
- Memory: Available MB. This counter specifies the available memory within the VM.
- SQL Server: Buffer Cache Hit Ratio. This counter tells you the percentage of pages found in the buffer cache without having to read from disk. A rate of 90 percent or higher is desirable.
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.
