I have noticed this error occasionally in the SQL error log:
spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure.
I am using SQL Server 2016, SP1 CU5 (I am pushing for patching but the company is resistant).
Everything I have read points to non-CLR-specific memory pressure. There are suggestions around changing the MemToLeave
setting in the start up parameters. Is this still the case for newer versions of SQL Server, or are there other recommendations?
Best Answer
Memory architecture was changed in SQL Server 2012 such that there was little need to worry about the
MemToLeave
setting anymore, especially if using 64-bit SQL Server. And, starting with SQL Server 2016 (which you are using), SQL Server is only available in 64-bit (see the "Note" at the top of the "What's new in Database Engine - SQL Server 2016" page). So, no, don't worry aboutMemToLeave
.Correct, "Memory Pressure" errors are not specific to SQLCLR. Those errors are not telling you the cause of memory pressure, but instead what is being impacted by there being memory pressure (which I doubt there is any possible way to truly have insight into the cause of anyway — I mean, if there are 10 processes taking up memory, which combination is really the cause? it's not necessarily what is taking up the largest chunk as that might be entirely valid). Memory pressure impacts other areas as well that might not show up in the error log, such as flushing the plan cache and/or buffer pool (i.e. data pages loaded into memory).
There are several built-in features that use SQLCLR, a partial list being the following:
FORMAT
PARSE
TRY_PARSE
AT TIME ZONE
(starting in SQL Server 2016)COMPRESS
(but notUNCOMPRESS
; starting in SQL Server 2016)One or more of those (or perhaps one that I did not list above) is what is being affected in your system. There are two clues, both within the
(master.sys[runtime].78)
part of that info, that tell us this:master
(assuming you would never, ever load custom assemblies intomaster
;-))the "owner" (i.e.
AUTHORIZATION
) of the assembly beingsys
(we cannot assign ownership of assemblies to eithersys
orINFORMATION_SCHEMA
as neither of those principals has anSID
). If you want to see the owner for each assembly, execute the following:What you can do is: