THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLOS Team

A blog for members of the SQL Server SQLOS team to share information and get your feedback.

SQL Server and Hyper-V Dynamic Memory Part 3


In parts 1 and 2 of this series we looked at the basics of Hyper-V Dynamic Memory and SQL Server memory management. In this part Serdar looks at configuration guidelines for SQL Server memory management.

Update 8/2/11: please also refer to the recently published white paper: Running SQL Server with Hyper-V Dynamic Memory - Best Practices and Considerations.

Part 3: Configuration Guidelines for Hyper-V Dynamic Memory and SQL Server


Now that we understand SQL Server Memory Management and Hyper-V Dynamic Memory basics, let’s take a look at general configuration guidelines in order to utilize benefits of Hyper-V Dynamic Memory in your SQL Server VMs.

Requirements

Host Operating System Requirements

Hyper-V Dynamic Memory feature is introduced with Windows Server 2008 R2 SP1. Therefore in order to use Dynamic Memory for your virtual machines, you need to have Windows Server 2008 R2 SP1 or Microsoft Hyper-V Server 2008 R2 SP1 in your Hyper-V host.

Guest Operating System Requirements

In addition to this Dynamic Memory is only supported in Standard, Web, Enterprise and Datacenter editions of windows running inside VMs. Make sure that your VM is running one of these editions. For additional requirements on each operating system see “Dynamic Memory Configuration Guidelines” here.

SQL Server Requirements

All versions of SQL Server support Hyper-V Dynamic Memory. However, only certain editions of SQL Server are aware of dynamically changing system memory. To have a truly dynamic environment for your SQL Server VMs make sure that you are running one of the SQL Server editions listed below:

·         SQL Server 2005 Enterprise

·         SQL Server 2008 Enterprise / Datacenter Editions

·         SQL Server 2008 R2 Enterprise / Datacenter Editions

Configuration guidelines for other versions of SQL Server are covered below in the FAQ section.

Guidelines for configuring Dynamic Memory Parameters

Here is how to configure Dynamic Memory for your SQL VMs in a nutshell:

Hyper-V Dynamic Memory Parameter

Recommendation

Startup RAM

1 GB + SQL Min Server Memory

Maximum RAM

> SQL Max Server Memory

Memory Buffer %

5

Memory Weight

Based on performance needs

 

Startup RAM

In order to ensure that your SQL Server VMs can start correctly, ensure that Startup RAM is higher than configured SQL Min Server Memory for your VMs. Otherwise SQL Server service will need to do paging in order to start since it will not be able to see enough memory during startup.

Also note that Startup Memory will always be reserved for your VMs. This will guarantee a certain level of performance for your SQL Servers, however setting this too high will limit the consolidation benefits you’ll get out of your virtualization environment.

Maximum RAM

This one is obvious. If you’ve configured SQL Max Server Memory for your SQL Server, make sure that Dynamic Memory Maximum RAM configuration is higher than this value. Otherwise your SQL Server will not grow to memory values higher than the value configured for Dynamic Memory.

Memory Buffer %

Memory buffer configuration is used to provision file cache to virtual machines in order to improve performance. Due to the fact that SQL Server is managing its own buffer pool, Memory Buffer setting should be configured to the lowest value possible, 5%. Configuring a higher memory buffer will prevent low resource notifications from Windows Memory Manager and it will prevent reclaiming memory from SQL Server VMs.

Memory Weight

Memory weight configuration defines the importance of memory to a VM. Configure higher values for the VMs that have higher performance requirements. VMs with higher memory weight will have more memory under high memory pressure conditions on your host.

Questions and Answers

Q1 – Which SQL Server memory model is best for Dynamic Memory?

The best SQL Server model for Dynamic Memory is “Locked Page Memory Model”. This memory model ensures that SQL Server memory is never paged out and it’s also adaptive to dynamically changing memory in the system. This will be extremely useful when Dynamic Memory is attempting to remove memory from SQL Server VMs ensuring no SQL Server memory is paged out.

You can find instructions on configuring “Locked Page Memory Model” for your SQL Servers here.

Q2 – What about other SQL Server Editions, how should I configure Dynamic Memory for them?

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

Tune Maximum Memory and Memory Buffer based on the other workloads running on the system. If there are no other workloads consider using Static Memory for these editions.

Q3 – What if I have multiple SQL Server instances in a VM?

Having multiple SQL Server instances in a VM is not a general recommendation for predictable performance, manageability and isolation. In order to achieve a predictable behavior make sure that you configure SQL Min Server Memory and SQL Max Server Memory for each instance in the VM. And make sure that:

·         Dynamic Memory Startup Memory is greater than the sum of SQL Min Server Memory values for the instances in the VM

·         Dynamic Memory Maximum Memory is greater than the sum of SQL Max Server Memory values for the instances in the VM

Q4 – I’m using Large Page Memory Model for my SQL Server. Can I still use Dynamic Memory?

The short answer is no. SQL Server does not dynamically change its memory size when configured with Large Page Memory Model. In virtualized environments Hyper-V provides large page support by default. Most of the time, Large Page Memory Model doesn’t bring any benefits to a SQL Server if it’s running in virtualized environments.

Q5 – How do I monitor SQL performance when I’m trying Dynamic Memory on my VMs?

Use the performance counters below to monitor memory performance for SQL Server:

Process - Working Set: This counter is available in the VM via process performance counters. It represents the actual amount of physical memory being used by SQL Server process in the VM.

SQL Server – Buffer Cache Hit Ratio: This counter is available in the VM via SQL Server counters. This represents the paging being done by SQL Server. A rate of 90% or higher is desirable.

Conclusion

These blog posts are a quick start to a story that will be developing more in the near future. We’re still continuing our testing and investigations to provide more detailed configuration guidelines with example performance numbers with a white paper in the upcoming months.

Now it’s time to give SQL Server and Hyper-V Dynamic Memory a try. Use this guidelines to kick-start your environment. See what you think about it and let us know of your experiences.

- Serdar Sutay

Originally posted at http://blogs.msdn.com/b/sqlosteam/
Published Wednesday, March 09, 2011 9:33 AM by SQLOS Team
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam said:

I am disappointed that SQL 2008 R2 Web Edition does not appear to support true dynamic memory the same as Enterprise and Datacenter SQL versions, as it is a specific edition for web hosting environments which are most likely to utilise dynamic memory capabilities. Just had a painful experience of this today before seeing this guide (Dynamic memory range of 1Gb - 6Gb, SQL decided to only allocate 768Mb of RAM and no more, then crashed out requests with insufficient pool to run this query errors and maxed CPU at 100%).

Please give consideration to extending the SQL Server 2008 R2 Enterprise / Datacenter Edition true dynamic memory support to the 2008 R2 Web edition also in a future service pack for this reason.

March 22, 2011 1:35 PM
 

Guy Bowerman said:

Hi Adam, thanks for the comments.

One thing we're doing for Denali is enabling true dynamic memory support in Standard Edition. This should carry over to Web edition too.

I guess this doesn't address your immediate 2008 R2 need. I'll take this as a vote in favor of backporting this into a service pack. In the meantime is there any mitigation you can do like restarting SQL Server once memory has grown (not a long term solution but might provide a temporary workaround in some situations).

Guy

April 4, 2011 5:55 PM
 

RS said:

So in your experience, does dynamic memory for a SQL VM make it more efficient, faster and use less memory?

May 27, 2011 6:57 PM
 

SQLOS Team said:

Faster, no.

More efficient - depends what you mean. If you mean use memory more efficiently, then potentially, which leads to the last part.. use less memory, I will say yes, but it does depend on configuration settings, workload, the degree to which you can manually adjust settings, and your failover settings.

Without dynamic memory you have to configure the VM to use a fixed amount of memory and that's what you get. With dynamic memory you can configure the startup RAM to use less so there can be an initial saving.

What happens next depends on the SQL "max server memory" memory setting, the Hyper-V maximum RAM setting and the workload (as well as memory presure from other VMs on the host). For example, If you have a high "max server memory" setting and high workload you'll find yourself using the maximum memory, and to then use less and get any saving back you'd have to manually adjust down "max server memory" or experience strong memory pressure from other VMs so that SQL Server receives low memory signals and starts releasing memory.

- Guy

May 29, 2011 8:28 PM
 

Adam said:

Hi Guy, thanks for your reply and the news that you are looking at this for a future release which is good to hear.

Where this becomes a problem is in clustered / high availability environments where SQL is working in an Active/Passive capability. For client e-commerce web hosting scenarios SQL Web Edition is the edition of choice for the majority of them due to the lower overall operating cost.

Without true dynamic memory support, this means that the passive server which has SQL stopped awaiting an emergency event (for hopefully five 9's of the time) has to have a large host memory allocation to it in order for it to function properly in a failover scenario. This is a monumental waste of resource and exactly what dynamic memory support in Hyper-V was designed to address.

In one such scenario, we have a pair of SQL 2008 Web Edition servers, both with 20Gb of RAM allocated under Hyper-V, and a third party SQL monitoring/failover tool. The live server currently has around 94% utilisation of physical memory (SQL is allowed 16Gb of the 20Gb, the rest for O/S and related software), the passive server has 5% utilisation of memory, wasting around 19Gb of memory resource on the off chance that a failover occurs!

Clearly in this scenario, true dynamic memory support in SQL web edition would mean an immediate Hyper-V saving of 19Gb of physical memory, no small saving.

October 19, 2011 9:37 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement