It’s a bummer that SQL Server 2012 Standard Edition only allows you 64GB of memory. And it’s good news that the limit goes up to 128GB of memory in SQL Server 2014.
But are you getting as much memory as you can out of Standard Edition?
Check out Microsoft KB 2663912:
Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. …. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the “max server memory” configuration.
This means that if you’re using Standard Edition of SQL Server 2012 or higher, you should be setting your max server memory higher than the “limit” — because you can use more than you might think!
So if I need to design a VM for a SQL Server that has 300 GB of data and is using SQL Server 2012 Standard Edition with its 64GB limit for data cache, to get the biggest benefit of memory for my licensing dollar I would want something like:
- 96GB of memory for the VM
- SQL Server “max server memory (MB)” set at 88064 (86GB)
Different workloads will use different amounts of memory in non-buffer pool caches — and not all workloads need 64GB of data cache, of course. But for those that do, don’t think “max server memory (MB)” is the cap on all your memory — for SQL Server 2012 and higher, it’s the cap for your buffer pool.
Thanks to Bob Ward , who mentioned this in a recent talk at SQL Intersection. (And I suspect he’s the one behind this great KB.)
27 Comments. Leave new
Hey, I’m sorry but I cannot follow your sample 🙂
300 GB vs 96 GB vs 86 GB ?
Can you be more specific about which number is confusing? I tried to be as clear as possible in the article, so I’m just not sure how to answer without essentially reposting the article in a comment, and if the article is confusing then that probably wouldn’t help much! If you can ask a more specific question then that might help me out.
I think he wants to know how you got the 96GB and 86GB from having 300GB of data.
Sorry, didn’t get to the next message before I posted. I looked for a response to you, not to him.
The 300 is just a semi arbitrary ballpark estimate for a fairly large size database to still be using Standard edition. 86GB is the limit for the data that standard edition can cache. Therefore if you set your max server memory setting to 96GB SQL Server can actually use all 86 for caching data and still have 10GB to play with for plan cache and other items it needs memory for. Hope that clears things up for you homie.
Gah I typed my numbers wrong I mean 64 for the limit and 86 for the max and 96 for the VM.
Kendra, I think that he might be asking how you came up with the numbers 96GB of memory for the VM
SQL Server “max server memory (MB)” set at 88064 (86GB) for a SQL Server with 300 GB of data and using SQL Server 2012 Standard Edition with its 64GB limit for data cache. Is there a specific proportion or formula that you use when you are designing a VM in order to come up with these numbers? (Norbert, please correct me if I misunderstood you, but I was curious myself).
Oooh, thanks, got it!
If I need 64GB for data cache, how much do I need for everything outside this, such as procedure cache, metadata cache, etc? Procedure cache alone can be fairly substantial on some systems. Query workspace memory grants can also consume a lot of memory.
These things absolutely vary widely by usage, I just put together an example of “if I had to guess for a SQL Server where memory was important to performance, these would be some general numbers.”
Thanks, Kendra – that helped!
Yeah Pamela, you are right … why exactly that sizes ?
Got it. I think I answered it above. The 300GB for data is just “a number well above 64GB, because we need something for an example.”
This is awesome, and I have a server I can implement this on.
So the point is that it’s more than 64 GB, am I right?
Yes! Exactly. Specifically that “max server memory (MB)” is more than 64GB.
What should I specify for Max Memory if my SQL Server box only has 64GB on it? I don’t want to specify Max Memory above what the box has on it, do I? My databases on that box are in the hundreds of GB, as well. Thanks!
We typically recommend leaving 10% or 4GB of memory free for the operating system (whichever is larger) to start with if the box is dedicated to the SQL Server engine. So if only 64GB of Windows, you’d set max server memory at 57GB in that case.
Thanks… that’s exactly what I’ve done. Nice to have validation, though.
Doesn’t the server os version also come into play. Doesn’t Win 2008 R2 std have a memory max of 32 GB. There is mention of this in the KB.
Yep, all the memory in the world won’t help you if Windows doesn’t see it!
I always thought max server setting was related to the buffer pool solely and nothing else. So if I increase it how can SQL use the additional memory towards the procedure cache since I thought that was managed outside of this setting.
Max server memory is NOT just the buffer pool. There’s a table in the KB I link to that spells out what is governed by max server memory for different versions of SQL Server — check out http://support2.microsoft.com/kb/2663912/
Execution plans could be either Single-page allocations or Multi-page allocations for larger plans, so note that prior to SQL Server 2012, it’s complicated.
Hi, coming a little late into this but we’ve got 256Gb available so following the comments above should lead me to think that we set our max mem for the VM to 250Gb and set max mem for SQL to 240Gb for instance?
Would i see a benefit with these settings?
Seems the SQL Server 2012 only will occupy MAX 180G Memory, Even I set 256G for SQL , Total 350G Memory for
Windows Server 2012 R2, does anybody can tell me why?
Xiong, for Q&A, head over to https://dba.stackexchange.com/
Thanks!
I’ve got a standard edition 2016 on a server with 256GB ram.
Will sql server use the 256? or only 128?
If I set the sql server to take 180…it will take 180…but will it also use it?
Should I set maximum memory to 128 because the standard edition will not use more?
Hi Titus. This post is about SQL Server 2012. For other versions, you’ll want to find other resources. Cheers!
Hi Brent – myeah:))) one small detail