SQL Server 2008 R2 memory consumption

Question

Hi, hoping to get some advice on an 'issue' I'm currently facing with regard to memory usage.

Quick bit of background would probably help. My company has recently implemented a new 2-node SQL Server 2008 R2 cluster running 5 separate SQL Server clustered instances in a Windows 2008 R2 o/s. The people who commissioned the cluster and who hired a third-party to build it completely ingored the in-house DBA expertise until it came time to 'hand it over'. In other words dump it in the laps of myself and a colleague who knew nothing about the resource requirements of the databases and associated applications and are both 'common-or-garden' DBAs rather than experts. Sound familiar? Anyway, gripe over.

One instance in particular - Enterprise Vault in case you're interested - has eaten up most of the memory on the node on which it's currently running (48GB available) and is sitting at about 42GB. The other instances have pretty much eaten up all the memory on the other node (again 48GB available) . Both nodes are now at about 96% memory usage.

Prior to that active/active configuration all instances were running on a single node in active/passive mode. However that is no longer possible given the total memory requirements of all instances is currently 74GB, somethat more than the 48GB available on each node. In that mode performance went through the floor and 'naturally' the DBAs were given dog's abuse and 'asked' to fix it.

I've therefore had to take evasive action in balancing the load across both nodes and setting Max Memory limits which were left on their default settings. I've also gone  through all  the usual checks and looked at all the various metrics to tell me if there was something causing the memory requirements to be over and above the normal and natural one utilised by Buffer Cache and Procedure Cache for an instance whose databases total nearly 100GB in size.

All the ususal suspects are normal. Memory usage from sys.dm_os_sys_memory tells me the Cached Page Count is 2471715 and 1116275 for the biggest databases. (Is that high?) and The 'clean' page count for those databases, from sys.dm_os_process_memory, are 2471715 and 1107927 respectively. (Again is that high?)

Buffer Cache hit Ratio does seem suspiciously high though at 100%. Shouldn't it be 90%+? So too is Page Life Expectancy at 458307ms. I know it should ideally be over 300-400. Wasn't expecting it to be quite as big as that though. Is that normal, or rather not abnormal, for databases of this size.

There are certainly some large objects in buffer cache and a number of SPs have high Total Logical Read counts but basically, as far as I can see, the simple fact of the matter is that Enterprise Vault has some big databases and they need a lot of memory. I can, of course, control Buffer Cache consumption by using the Max Memory setting but there is a limit to the size of Page File I can set in the current hardware configuration so I could see excessive paging if I set this too low. However I thought I'd get some advice anyway to see if there's anything obvious I've missed checking in a configuration that I've only just been introduced to.

Regards,

Gordon.

Answer 1

You are on the right track.  You will need to reduce the "Max Memory" setting on each instance to reduce the buffer cache until the server stops page swapping.  Depending on many variables, like SSIS, linked servers, usage, etc, there is no way to tell what the value should be. You will need to try it until you find a value that works and performance is acceptable.

Answer 2

Hmm, looks like my reply hasn't posted. Will try again. Apologies if it gets posted twice.

Thanks for the reponse, tom, and apologies for taking so long to reply. Put my back out just after posting so have been recuperating since.

As I'm sure you will appreciate shortly I'm no expert. I had been under the impression that once SQL Server had used up whatever its Max Memory value had ben set to then it would start paging excessivley anyway as it needs more memory.

I understand that that the bulk of the memory utilisation is being used for buffer cache. Is it then not the case that once this has been used up then paging will occur?

Regards,

Gordon.

Answer 3

Looks like posting isn't happening with Firefox 10.0.2.

Answer 4

You do not want a SQL Server page swapping at all (there is some internal Windows paging for temp space) this will cause significant performance issues.

You need to reduce the "max memory" until the server stops paging.

Please see:

http://support.microsoft.com/kb/321363

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

Answer 5

I think I'm misunderstanding this. The problem, as i see it, is that SQL Server doesn't have enough memory too start with, or at least barely enough. Isn't it the case that, if I reduce the size of the Buffer Cache (by setting a Max Memory ceiling) in order to reduce paging by other caused processes, then SQL Server needs to carry out more i/o because less of the data is in memory and that, in itself, causes more paging? If I had enough memory in the first place I would then be able to set Max Memory to a value at or slightly above what SQL Server actually needs. At the moment it seems to 'need' pretty much all the physical memory available.

Gordon.

Answer 6

The "max memory" only controls the buffer cache size.  This is not critical to the operation of SQL Server.  It will simply make it cache less records and may affect performance, but SQL will continue to run normally.  Adding memory to SQL Server almost always improves performance because it add buffer cache.

There is not a correlation between database size and memory buffer cache. 

Answer 7

Many thanks for the input, Tom. As the instance in question is using up all of its memory allocation - i.e. the v alue set as Max Memory - I am seeing excessive paging however the imminent memory upgrade will resolve that. Much clearer on how SQL Server consumes memory now. Many thanks.

Gordon.

Answer 8

Buffer Cache Hit Ratio is a useless counter on most systems for monitoring memory.  It actually tells you more about the health of the read ahead mechanisms inside of SQL Server:

http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

If you PLE is in the thousands and higher across the instances you likely don't need to add memory, you just need to make them stop contending against each other for memory by properly setting the configuration options which it sounds like you've already started doing.  Keep in mind that max server memory is only buffer pool space, so you need to monitor the total memory usage as well as the available memory on the server using Memory\Available MBytes to make sure that you've configured memory correctly to prevent the OS from getting into memory pressure and triggering paging.  I cover memory configuration in depth in Chapter 4 of my book which you can download for free in PDF from the following link:

Troubleshooting SQL Server: A Guide for Accidental DBAs

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 9

As Jonathan said, based on the information you gave, your server has plenty of RAM. You need to set the max memory setting on each instance to a value which balances performance and RAM.  Each instance requires dedicated RAM.  The instances to not interact in any way. 

Answer 10

As others have said you need to set the max memory for all instances so the sum is several GB less than the total physical memory, the remainder left for Windows and also for other memory allocations for SQL Server, as well as any other software also running on the servers, if any.

Leaving max memory at default on the instances sounds like a major error by whoever set them up.  SQL Server's normal behavior is to grab all available memory up to the max, and if the max is the default "just grow!", SQL Server will grab more than is physically available, which then causes Windows to support the request by paging, and your performance will go to nothing.

So, is 48gb RAM enough for a total of your instances?  I dunno.  How large are the databases, and how active, are they OLTP, ETL, or OLAP, etc.  But even if you cut your large instance down to say 32gb, that's still a pretty healthy number.  And even if it does cause a need for more IO, SQL Server is *much* happier managing its own IO, than it is when you let it grab virtual memory and then letting Windows page it.

HTH,

Josh