Maximum SQL Server memory set to 12 GB, but it still goes over 12 GB. How do I prevent SQL Server going over max memory limit?

Question

User 'mrdenny' wrote that after you set SQL Server maximum memory...

Now this isn't going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the execution plan cache. Things like CLR, Full Text, the actual memory used by the SQL Server exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting.

My problem is that my server is going over the set 12 GB memory and if things like CLR, Full Text, and Server exe files taking additional memory.

How do I configure SQL Server, so it does not go over the set max limit?

I was told there was a way to do it, but I cannot find a solution after searching on Google.

Answer 1

My problem is that my server is going over the set 12GB memory and if things like CLR/Full Text/Server exe files taking additional memory.

What Denny Cherry wrote is true for SQL Server version from 2005 to 2008 r2. From SQL Server 2012 onward there was considerable change in SQL Server memory allocation. Memory for SQLCLR is allocated from max server memory setting in SQL Server 2012 not outside of it. BOL says that from SQL Server 2012 onward

Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

Prior to SQL 2012, the buffer pool both “managed” memory and was a consumer of memory for database pages. It’s management of memory meant it allocated 8Kb pages of memory for other consumers like plan cache. Roll forward to 2012+, the buffer pool is a pure consumer of memory from SQLOS which manages all of the memory.

Please tell me how to configure SQL Server, so it does not go over the set max limit.

This is not possible. In SQL Server 2012 if you are not using third party linked servers providers or if any DLL which is not Microsoft provided is not loaded in SQL Server address space there is less chance that SQL Server can consume memory outside max server memory setting. But the fact is you cannot control SQL Server not to utilize memory outside buffer pool. That is how it is designed.

Answer 2

There's no exact way to do this. The setting you apply to max server memory only manages some of SQL Server's memory, it will still use other allocations depending on what you're doing. To limit this you can minimize your usage of things that use memory external to this (CLR, full text, etc), but you can't eliminate all of it. You can play with max server memory to find the right balance, but again, there's no way to predict exactly how this pan out, and may take many iterations to get right. Start with 9 GB let's say (though you can probably get more specific by starting with (12 - (current memory usage - 12)) - so if current memory usage is 16 GB, try starting with 8 GB.