SQL SERVER 2017 DEVELOPER EDITION LIMITATIONS LICENSE
(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.) 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. Now let’s consider the RAM limit of all non-Enterprise editions of SQL Server, as specified in the license agreement. There’s a lot of other stuff going on too.
The main takeaway here is that Max Server Memory is not limited to the buffer pool. 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. 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). 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. 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.
On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. For the sake of this post, I am talking specifically about SQL Server 2012 and higher. Joey D’Antoni was quick to remind me that this only applies to SQL Server 2012 and higher, which I gratefully acknowledge. The RAM limit Standard Edition can use *is* just for the buffer pool. Reminder: Max Server Memory is *not* just for the buffer pool. Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation: