6

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?

10

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?)
  • Soloman, thank you for this really appreciate the detail and the help. Filtering the error log i can see this happens maybe 3/4 times per month and has increased a little this month (Well August) I have been asking for more memory in this server for a while now I will add this onto my reasons list. – Stockburn Sep 4 at 2:01
  • And No 'I' Wouldn't load anything into the Master DB .... but this place had no DBA before me just someone who 'Did SQL' – Stockburn Sep 4 at 2:06
  • @Stockburn Glad to be of help :-). Yeah, i would consider 3 - 4 times per month a rather low priority, but certainly use it for leverage to get more RAM. And regarding master, that's why I pointed out the sys "owner" of the Assembly, because nobody, not even someone who had no clue what to do / not to do, would be able to do that part. Of course, technically none of this rules out a custom SQLCLR object causing the memory pressure, but I'd think that you'd see that assembly get unloaded as well, at least sometimes. No other assembly getting unloaded indicates SQLCLR isn't the problem. – Solomon Rutzky Sep 4 at 20:58
  • Thanks mate, fingers crossed this helps me get more RAM! – Stockburn Sep 5 at 5:35
7

Might look into this: https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide. Especially the query on what's currently allocated. It will give you somewhere to focus diagnostics on.

I've had this problem before, and it ended up being repeated calls to a CLR procedure that would never close itself down when it was done.

  • Thanks Adam! Will have a dig into this, appreciate the help. – Stockburn Sep 4 at 2:02

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.