Solved

max server memory for SQL 2008 R2 on VM

Posted on 2013-07-01
7
585 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 36

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

738 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