Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation:
Reminder: Max Server Memory is *not* just for the buffer pool. The RAM limit Standard Edition can use *is* just for the buffer pool.
Joey D’Antoni was quick to remind me that this only applies to SQL Server 2012 and higher, which I gratefully acknowledge. For the sake of this post, I am talking specifically about SQL Server 2012 and higher.
Let’s break this down.
On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. This value should not be left at the default 2.1 petabytes, because at that value SQL Server will use all the memory you allow it (plus a little bit more if it needs to), and once it has it, it doesn’t release it back to the operating system without a fight.
What does Max Server memory control, though? According to the documentation, it’s the buffer pool, compilation memory, all the caches including the plan cache, and a bunch of other stuff. Here’s a quote:
Max Server Memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.
The main takeaway here is that Max Server Memory is not limited to the buffer pool. There’s a lot of other stuff going on too.
Now let’s consider the RAM limit of all non-Enterprise editions of SQL Server, as specified in the license agreement. At the time of this writing, using Standard Edition as an example, the license has a RAM limit of 128GB, which is specifically the maximum memory available for the buffer pool only. (This clarification in the documentation occurred after the release of SQL Server 2016 Service Pack 1 last year, but the license limit has referred to the buffer pool since at least SQL Server 2012.)
This means that in a server with Standard Edition and a lot of RAM, you could set the Max Server Memory configuration setting higher than 128GB comfortably, and SQL Server will use more than 128GB perfectly legally, because there’s a lot more to Max Server Memory than just the buffer pool.
So when I read in forums and elsewhere that the license limit of 128GB for Standard Edition is the highest you can set Max Server Memory, this is demonstrably wrong.
To make this more interesting, SQL Server 2016 and higher supports Columnstore and In-Memory OLTP in non-Enterprise editions anyway, which means that you can easily consume all the physical RAM on a server, even if your buffer pool is using the legal limit.
Tell me your licensing story on Twitter at @bornsql.