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

Are end users causing IT problems again?

You’ve taken the time to design and update all your end user’s email signatures, only to find out they’re messing up the HTML, changing the font and ruining the imagery. What can you do to prevent this? Find out how you can save your signatures from end users today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to search 5 23
SQL server is using more virtual memory. 5 67
recover sqlserver db 8 54
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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now