Solved

SQL Server 2008 R2 Memory Question

Posted on 2014-03-10
3
420 Views
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
0
Comment
Question by:jtbrown1111
3 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 39918777
Here is information on managing SQL server's memory:

http://technet.microsoft.com/en-us/library/ms178067.aspx
0
 
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.
0
 
LVL 11

Accepted Solution

by:
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.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
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 …

786 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