Solved

max server memory for SQL 2008 R2 on VM

Posted on 2013-07-01
7
589 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

Question has a verified solution.

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

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…
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

726 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