?
Solved

max server memory for SQL 2008 R2 on VM

Posted on 2013-07-01
7
Medium Priority
?
595 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 668 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 1332 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 1332 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
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…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

765 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