Solved

max server memory for SQL 2008 R2 on VM

Posted on 2013-07-01
7
584 Views
Last Modified: 2013-07-08
Hi all,
I'm looking for some current advice on memory configuration for the following environment:

SQL 2008 R2, running on Windows Server 2008 R2 Enterprise x64.  This is a VM with 20 GB RAM, managed by a VMware hypervisor (memory ballooning is running on my whole environment).

SQL server consistently uses 95% of available memory, despite several of my memory increases to the VM.  I understand this is normal.  I understand that I should NOT use "Lock pages in memory" in a virtual environment where memory ballooning is enabled.  

What I haven't found online is a definite answer on whether I should set "max server memory" for this SQL config.  If so, how much?  

Thanks!
0
Comment
Question by:cuiinc
7 Comments
 
LVL 9

Accepted Solution

by:
edtechdba earned 167 total points
ID: 39292052
Have you considered limiting the amount of memory that is used by the system for file caching?

Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067(v=sql.105).aspx

And this may help you determine how to better balance your memory usage in SQL Server 2008 R2:

Memory Architecture
http://msdn.microsoft.com/en-us/library/ms187499(v=sql.105).aspx
0
 
LVL 18

Assisted Solution

by:Matthew Kelly
Matthew Kelly earned 333 total points
ID: 39292053
You are correct in that there is nothing wrong with SQL using a very high percentage of available memory: it is normal. This is an additional reason, beyond security, it is best to keep the SQL service on its own VM instance and have a separate instance for the webserver etc which may have different hardware utilization requirements.

There are circumstances where you would want to set maximum memory usage. I have done it when running multiple instances of SQL Server on a single machine. This MSDN article confirms my experience, which for example states it is a bad idea not to set limits when running multiple instances: http://msdn.microsoft.com/en-us/library/ms178067.aspx

If the VM you are running is dedicated to the SQL service (which it should be) and you are only running one instance of SQL Server, there is no need to set the limits.
0
 
LVL 6

Expert Comment

by:Vijay Pratap Singh
ID: 39292091
http://msdn.microsoft.com/en-us/library/ms178067.aspx

This article will help you to get information about architecture based memory usage and how to restrict the memory usage manuallly
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 35

Expert Comment

by:Miguel Oz
ID: 39295470
I will be reluctant to use a VM to host SQL server due to memory consumption you are mentioning  and I/O usage, for further details check:
http://www.sqlsolutions.com/articles/articles/SQL_Server_and_VMware-A_Potentially_Fatal_Combination.htm

A VM is a good choice when your SQL server VM does not have too much traffic/usage. Find out how your server is being used before making any decisions.
0
 
LVL 1

Author Comment

by:cuiinc
ID: 39298679
wow, I haven't heard of this sort of danger before, mas_oz2003.  

does anyone else share this opinion?  if so, what are viable HA alternatives?
0
 
LVL 18

Assisted Solution

by:Matthew Kelly
Matthew Kelly earned 333 total points
ID: 39298725
The article references running SQL in a VMPlayer virtual machine which isn't a true enterprise virtual environment, so I wouldn't expect it to have the best performance under heavy utilization. VMPlayer is no different in concept than Windows Virtual PC. Both of those are basically just desktop applications that create nothing more than images stored on the hard drive that run in memory. They are fine for pulling up desktop operating systems to perform specific tasks while the end user is logged in but really aren't designed to run server environments (even though they can).

Windows Hyper-V in 2008R2 for example has no problem. All kinds of documentation related to that fact: https://www.google.com/search?q=hyper+v+sql+server+2008+performance
0
 
LVL 1

Author Closing Comment

by:cuiinc
ID: 39308961
thanks All!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question