Solved

SQL Server 2008 R2 Memory Question

Posted on 2014-03-10
3
422 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
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 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…

828 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