Solved

max server memory for SQL 2008 R2 on VM

Posted on 2013-07-01
7
583 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
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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
A procedure for exporting installed hotfix details of remote computers using powershell
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

803 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