[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL Server 2008 R2 Memory Question

Posted on 2014-03-10
Medium Priority
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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
Viewers will learn how the fundamental information of how to create a table.

650 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