6

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.

|improve this question
  • 2
    which version of sql server? – swasheck Feb 6 '16 at 18:45
  • How much more usage are you seeing? If it's 100 MB, you proably shouldn't care. (Unless your server only has 12 GB of memory, which would beg the question why you set it that high.) This is an optimization question. Trying to solve it in the abstract isn't especially useful. – jpmc26 Feb 7 '16 at 4:46
9

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.

|improve this answer
7

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.

|improve this answer
  • 1
    "You can play with..." - that's correct, the only answer Microsoft has given us is to turn off everything you don't need to minimize both variance and usage, and then guess! – Anti-weakpasswords Feb 6 '16 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.