SQL Server Memory – Preventing Usage Over Max Limit

memorysql server

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.

Best Answer

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.

Related Question