asked
Effects of min and max server memory on SQL CLR
Hi, please help me out with this situation. Thank you very much. - SQL Server 2014 - Database server physical memory: 500GB - Minimum server memory setting: 400GB - Maximum server memory setting: 480GB We hosted SQL CLR in database, and cache an object that require 10GB (I heard that in CLR it will double up the required memory) in the database via SQL CLR so that we no need to select by query every time when we need the data. However, appdomain unload occurred frequently cause the cached object dropped. May I know is this situation caused by the server memory setting? The DB memory usage is high since we have a lot client request on web and report and other app. Is it possible because of the SQL server don't release memory below that min server memory setting, so sacrifice the SQL CLR allocation? Your help is greatly appreciated. Thank you. ClerkType virtual_memory_reserved_kb virtual_memory_committed_kb awe_allocated_kb shared_memory_reserved_kb MEMORYCLERK_SQLBUFFERPOOL 207963824 1048576 22917744 0 MEMORYCLERK_SQLCLR 43769072 14853380 0 0 MEMORYCLERK_SOSMEMMANAGER 6006532 4875396 1130312 0 OBJECTSTORE_LOCK_MANAGER 2099200 2099200 0 0 MEMORYCLERK_SQLSTORENG 23168 0 23168 0 MEMORYCLERK_XE_BUFFER 2944 2944 0 0 MEMORYCLERK_XE_BUFFER 2944 2944 0 0 MEMORYCLERK_XE_BUFFER 2944 2944 0 0 MEMORYCLERK_SQLGENERAL 0 0 0 0 MEMORYCLERK_SQLQUERYCOMPILE 0 0 0 0
the SQL CLR allocation is taken from virtual memory and is independent of, and in addition to, the SQL min/max memory settings. Your memory settings are not clear to me. You mention that the maximum memory is set to 400GB and the minimum memory is set to 500GB on a server with 500GB of RAM. There is another mention of memory setting of 480GB - what is that one? At any rate, if the minimum SQL memory is set to 500GB and the server has 500GB, then Windows will frequently send low memory notifications to SQL and it will unload as much virtual memory as it can, which means unloading your CLR AppDomain. It will not unload buffer pool memory any lower than the minimum SQL memory, possibly 500GB in your case This MSDN blog indicates that there could be seven distinct messages around AppDomains unloading. You didn't post any error messages, but do your errors line up with any of these? -
https://blogs.msdn.microsoft.com/psssql/2013/01/30/appdomain-unloading-messages-flooding-the-sql-server-error-log/ This MSDN blog covers CLR memory usage and how it can be unloaded in response to low server memory-
https://blogs.msdn.microsoft.com/sqlclr/2006/03/24/memory-usage-in-sql-clr/
You bet. With 500GB of physical memory, SQL allowed to use up to 480GB of memory, and your CLR object using 10-20GB, what is left for the operating system? You will need to reduce the max SQL memory or increase the server physical memory in order to provide enough memory for the SQL buffer pool, your CLR object *and* the operating system. With 500GB RAM, you might want to leave 66GB available for the OS. With the 20GB for the CLR object, that would put your max SQL memory setting at about 414GB. Couple more links for you - these on where to set max memory:
http://sqlblog.com/blogs/tibor_karaszi/archive/2014/06/01/53088.aspx
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
Hi KenJ, that should be Database server physical memory: 500GB; Minimum server memory setting: 400GB; Maximum server memory setting: 480GB Sorry for the bulleted list bring out wrong information. The error message is something like (XXX.dbo[runtime].125) is marked for unload due to memory pressure. Thanks for your explanation and links, they help me a lot. :)
17 People are following this question.
