SQL Server and Hyper-V Dynamic Memory - Part 1
SQL and Dynamic Memory Blog Post Series
Hyper-V Dynamic Memory is a new feature in Windows Server 2008 R2 SP1 that allows the memory assigned to guest virtual machines to vary according to demand. Using this feature with SQL Server is supported, but how well does it work in an environment where available memory can vary dynamically, especially since SQL Server likes memory, and is not very eager to let go of it? The next three posts will look at this question in detail.
In Part 1 Serdar Sutay, a program manager in the Windows Hyper-V team, introduces Dynamic Memory with an overview of the basic architecture, configuration and monitoring concepts. In subsequent parts we will look at SQL Server memory handling, and develop some guidelines on using SQL Server with Dynamic Memory.
Part 1: Dynamic Memory Introduction
In virtualized environments memory is often the bottleneck for reaching higher VM densities. In Windows Server 2008 R2 SP1 Hyper-V introduced a new feature “Dynamic Memory” to improve VM densities on Hyper-V hosts. Dynamic Memory increases the memory utilization in virtualized environments by enabling VM memory to be changed dynamically when the VM is running.
This brings up the question of how to utilize this feature with SQL Server VMs as SQL Server performance is very sensitive to the memory being used. In the next three posts we’ll discuss the internals of Dynamic Memory, SQL Server Memory Management and how to use Dynamic Memory with SQL Server VMs.
Memory Utilization Efficiency in Virtualized Environments
The primary reason memory is usually the bottleneck for higher VM densities is that users tend to be generous when assigning memory to their VMs. Here are some memory sizing practices we’ve heard from customers:
· I assign 4 GB of memory to my VMs. I don’t know if all of it is being used by the applications but no one complains.
· I take the minimum system requirements and add 50% more.
· I go with the recommendations provided by my software vendor.
In reality correctly sizing a virtual machine requires significant effort to monitor the memory usage of the applications. Since this is not done in most environments, VMs are usually over-provisioned in terms of memory. In other words, a SQL Server VM that is assigned 4 GB of memory may not need to use 4 GB.
How does Dynamic Memory help?
Dynamic Memory improves the memory utilization by removing the requirement to determine the memory need for an application. Hyper-V determines the memory needed by applications in the VM by evaluating the memory usage information in the guest with Dynamic Memory. VMs can start with a small amount of memory and they can be assigned more memory dynamically based on the workload of applications running inside.
Overview of Dynamic Memory Concepts
· Startup Memory: Startup Memory is the starting amount of memory when Dynamic Memory is enabled for a VM. Dynamic Memory will make sure that this amount of memory is always assigned to the VMs by default.
· Maximum Memory: Maximum Memory specifies the maximum amount of memory that a VM can grow to with Dynamic Memory.
· Memory Demand: Memory Demand is the amount determined by Dynamic Memory as the memory needed by the applications in the VM. In Windows Server 2008 R2 SP1, this is equal to the total amount of committed memory of the VM.
· Memory Buffer: Memory Buffer is the amount of memory assigned to the VMs in addition to their memory demand to satisfy immediate memory requirements and file cache needs.
Once Dynamic Memory is enabled for a VM, it will start with the “Startup Memory”. After the boot process Dynamic Memory will determine the “Memory Demand” of the VM. Based on this memory demand it will determine the amount of “Memory Buffer” that needs to be assigned to the VM. Dynamic Memory will assign the total of “Memory Demand” and “Memory Buffer” to the VM as long as this value is less than “Maximum Memory” and as long as physical memory is available on the host.
What happens when there is not enough physical memory available on the host?
Once there is not enough physical memory on the host to satisfy VM needs, Dynamic Memory will assign less than needed amount of memory to the VMs based on their importance. A concept known as “Memory Weight” is used to determine how much VMs should be penalized based on their needed amount of memory. “Memory Weight” is a configuration setting on the VM. It can be configured to be higher for the VMs with high performance requirements. Under high memory pressure on the host, the “Memory Weight” of the VMs are evaluated in a relative manner and the VMs with lower relative “Memory Weight” will be penalized more than the ones with higher “Memory Weight”.
Dynamic Memory Configuration
Based on these concepts “Startup Memory”, “Maximum Memory”, “Memory Buffer” and “Memory Weight” can be configured as shown below in Windows Server 2008 R2 SP1 Hyper-V Manager. Memory Demand is automatically calculated by Dynamic Memory once VMs start running.
Dynamic Memory Monitoring
In Windows Server 2008 R2 SP1, Hyper-V Manager displays the memory status of VMs in the following three columns:
· Assigned Memory represents the current physical memory assigned to the VM. In regular conditions this will be equal to the sum of “Memory Demand” and “Memory Buffer” assigned to the VM. When there is not enough memory on the host, this value can go below the Memory Demand determined for the VM.
· Memory Demand displays the current “Memory Demand” determined for the VM.
· Memory Status displays the current memory status of the VM. This column can represent three values for a VM:
o OK: In this condition the VM is assigned the total of Memory Demand and Memory Buffer it needs.
o Low: In this condition the VM is assigned all the Memory Demand and a certain percentage of the Memory Buffer it needs.
o Warning: In this condition the VM is assigned a lower memory than its Memory Demand. When VMs are running in this condition, it’s likely that they will exhibit performance problems due to internal paging happening in the VM.
So far so good! But how does it work with SQL Server?
SQL Server is aggressive in terms of memory usage for good reasons. This raises the question: How do SQL Server and Dynamic Memory work together?
To understand the full story, we’ll first need to understand how SQL Server Memory Management works. This will be covered in our second post in “SQL and Dynamic Memory” series.
Meanwhile if you want to dive deeper into Dynamic Memory you can check the below posts from the Windows Virtualization Team Blog:
https://blogs.technet.com/virtualization/archive/2010/03/18/dynamic-memory-coming-to-hyper-v.aspx
- Serdar Sutay
Comments
Anonymous
February 23, 2011
Good article. Anxiously awaiting part 2 so I can decide what to do with Dynamic Memory in our production sql environment. Thanks.Anonymous
February 26, 2011
Hi Serdar, looking forward to further posts in this series to get some guidance from the SQL Server team on how to use SQL Server with Hyper-V dynamic memory allocation. In tests with our 2008 R2 SP1 servers, I have seen a variety of problems: e.g. SQL Server 2005 Express does not start when at the recommended initial RAM setting of 512MB on a 2008 R2 SP1 Hyper-V guest OS or SQL Server 2008 R2 Express is continually asking the VMHost for more RAM, resulting in the VMHost reporting a "Memory Demand" above the "Assigned Memory". I have set the minimum Hyper-V dyamic RAM allocation for VM guests hosting a SQL Server instance to the same value as their maximum level for now which resolved our issues. Thanks, TimAnonymous
February 27, 2011
Hi Tim, where are you getting the recommended initial size of 512MB from? We'll look at recommended sizing in the 3rd part of this series, but we recommend at least 1GB + whatever you choose to set SQL Server "min server memory to". Regards GuyAnonymous
March 24, 2011
Hi Guy, sorry, I didn't see your comment until I checked back to see if parts 2 and 3 were up. I got the figure of 512MB as the recommended initial server RAM setting from "Hyper-V Dynamic Memory Configuration Guide" at technet.microsoft.com/.../ff817651(WS.10).aspx, which recommends this for Windows Server 2008 R2 VMs. The article does not address SQL Server at all, however. Having read parts 2 and 3 of this series, I now see the advice you mention. The blog posts are somewhat oriented around SQL Server Enterprise and Datacenter. Could you give a little more advice on tuning for SQL Server Standard and Express? Here is an example from a server, in this case, running SQL Server 2008 Standard Edition 64-bit (10.0.4000.0) on a Windows Server 2008 R2 Standard with SP1 VM. SQL Server min and max memory are currently at installation defaults, 0 MB and 2147483647 MB. The VM as a whole is configured with dynamic memory, with 1536 MB of Startup RAM and 2800 MB of Maximum RAM. Q2 in blog post 3 says, "Other editions of SQL Server do not adapt to dynamically changing environments. They will determine how much memory they should allocate during startup and don’t change this value afterwards. Therefore make sure that you configure a higher startup memory for your VM because that will be all the memory that SQL Server utilize". When SQL Server started on the machine, it was using under a GB of RAM, which seemed about expected based on SQL Server would tune for a system with a total RAM allocation (at that time) of 1536 MB and some other apps running as well. However, after 8 hours of light use, Resource Monitor reports sqlservr.exe has a Commit size of 1608 MB and a Working set of 1414 MB. In PerfMon, Target Server Memory is 1505 MB and Total Server Memory is 1314 MB. The VM has been allocated up to its full 2800 MB during this time as well.
- So, to clarify, SQL Server Standard/Express editions will not ever use more RAM than the Startup RAM figure assigned to the VM, but they will use up to that entire amount regardless of how they initially tune their memory allocation at startup?
- The blog posts talk a lot about setting SQL Server min and max RAM values, but the defaults are 0 and 2147483647. Is it suggested that it is better to now manually set these values for SQL Server when it is in a dynamically-assigned RAM VM, and, if so, to what kinds of values considering the overall RAM assigned to VM? For example, with the 1536 MB of Startup RAM, set SQL Server to a minimum RAM of 512 MB (considering Serdar's advice of "1 GB + SQL Min Server Memory") and with a VM maximum of 2800 MB, setting SQL Server to a maximum of something like 1536 MB or 2048 MB to ensure there is RAM left for other applications? Thanks Guy. Tim
- Anonymous
March 27, 2011
The comment has been removed - Anonymous
March 28, 2011
Hi Guy, Re. 1) Right, I was thinking "ever" meaning the SQL Server process lifetime only. However, any improvements to dynamic memory handling for 2008 R2 Standard or Express via future service packs would, of course, be great. :)
- Thanks for the idea of a scheduled downtime restart to pick up on additional Hyper-V allocated virtual RAM. I'll keep that in mind. Tim