Sql-server – Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

error logsql serversql-clrsql-server-2016

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 about MemToLeave.

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:

  • Datatypes:
    • HierarchyID
    • Geography
    • Geometry
  • Functions:
    • FORMAT
    • PARSE
    • TRY_PARSE
    • AT TIME ZONE (starting in SQL Server 2016)
    • COMPRESS (but not UNCOMPRESS ; starting in SQL Server 2016)
  • Features:
    • Change Data Capture
    • Dynamic Management Framework
    • Replication
    • Policy-Based Management
    • Master Data Services
    • SSISDB (the "Fuzzy Lookup" feature)

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:

  1. the database being master (assuming you would never, ever load custom assemblies into master ;-))
  2. the "owner" (i.e. AUTHORIZATION) of the assembly being sys (we cannot assign ownership of assemblies to either sys or INFORMATION_SCHEMA as neither of those principals has an SID). If you want to see the owner for each assembly, execute the following:

    SELECT asm.[name] AS [Assembly],
           USER_NAME(asm.[principal_id]) AS [Owner],
           USER_SID(asm.[principal_id]) AS [OwnerSID]
    FROM   sys.assemblies asm;
    

What you can do is:

  1. You mention that this error shows up "occasionally", but have not exactly defined how often this really is. Once an hour is different than once a day which is different than once per week. Memory pressure happens, so unless this is more than a few times per day, I wouldn't spend too much time worrying about it.
  2. Add more memory (either physically and/or allowing SQL Server to use more of the system memory if you currently have SQL Server constrained to a lesser amount)
  3. Analyze what is consuming memory outside of SQL Server to see if there are unnecessary processes on the server that can be offloaded to other servers, or perhaps shut-off entirely (i.e. are there other services being used, and/or remote desktop sessions that are then running SSMS, etc that are using lots of memory?)