SQL Server 2008 R2 Memory Question

Posted on 2014-03-10
Last Modified: 2014-03-21
I'm running SQL 2008 on a 2008 R2 server and no matter how much memory I give the VM it will use 95% of it.  The server doesn't have any additional roles it fills and is dedicated to primarily SQL role.

What is the best way to determine / control the amount of memory I should give to the VM without having any performance issues.    It currently has 24 and I can give it more if needed but just not a database expert.

Thanks ...

Joel T Brown
Question by:jtbrown1111
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
LVL 29

Expert Comment

ID: 39918777
Here is information on managing SQL server's memory:
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39919441
I'm running SQL 2008 on a 2008 R2 server and no matter how much memory I give the VM it will use 95% of it.
SQL Server by design will use all memory that you make available to it.

What is the best way to determine / control the amount of memory I should give to the VM without having any performance issues.
Give SQL Server all the memory you can afford and when that is not enough optimize your queries.
LVL 11

Accepted Solution

b_levitt earned 500 total points
ID: 39920797
While some memory is allocated per connection, per query, and other server processes, the bulk of the memory consumption comes from the buffer cache.  

The buffer cache contains the same datapages that exist on disk.  Ideally, a query is able to execute against already loaded data vs having to hit the slow disk subsystem.  Consequently, Sql server will leave as much of the database(s) as possible in the cache.  So if you've got 100GB db totals in disk, you'd need close to 100GB in memory before you'd start to see extra memory remain available (it does not arbitrarily consume all memory).

On physical hardware, this is normally a good thing - why not use memory that's sitting idle.  However, in a VM environment, you're taking memory headroom that the VM host might be able to use for another guest.

Now what SQL Server will NOT do is starve the operating system - if the OS needs memory, sql server will begin trimming the cache.  Technically speaking, if the VM host needs memory, it could "balloon" the SQL Server's VM, and steal some memory back.  But of course that could cause performance problems when you're not expecting it.

So, I'd say you probably want some understanding of the minimum amount of memory needed to keep performance acceptible and guarantee that amount to the sql server VM.   Basically, you need to be able to recognize a memory bottleneck.  You can back off the memory and look at counters like "Buffer Cache Hit Ratio".   The hit ratio should ideally remain in the high 90s.   For more info, a dozen articles would pop up under "sql server memory bottleneck" on google.

Finally if you've got more CPU overhead than memory (which is typical) and you have the enterprise edition of sql server, you could consider row and page compression, which compresses not only the space on disk, but the space in the cache as well.

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

Suggested Solutions

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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…

730 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