question

JohnWilber-5548 avatar image
0 Votes"
JohnWilber-5548 asked NielsBerglund-3563 commented

Frequent Error Message: "Another query caused the AppDomain <dbname>.dbo[runtime].n to be unloaded"

I'm currently working on SQL Server 2017 - Here's the @@version:

 Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64)
 Mar 13 2020 14:53:45
 Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) 

We keep getting jobs failing with error messages very similar to this:

2021-03-16 06:11:32.7483 -07:00 Error: .NET Framework execution was aborted. Another query caused the AppDomain Market.dbo[runtime].41 to be unloaded.

It also seems that several jobs fail with this error about the same time, and they all use CLR assemblies.

Obviously, there's a memory usage issue. We don't believe there is anything wrong with the code, however, we believe the assembly that is causing the issues is one that uses the HttpClient class. The procedure that calls this assembly typically calls the CLR function about 300 times in quick succession when it's run. To further complicate things, two copies of the stored proc are run simultaneously for greater throughput, for a total of about 600 calls to the CLR assembly in a short period of time.

Does anyone have any suggestions for how we can monitor memory usage by this specific assembly?
Is there a way to allocate more memory for the CLR assemblies?
Any other suggestions as to how we can make this more reliable?

Thanks,

John


sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JohnWilber-5548,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @JohnWilber-5548,

Is there a way to allocate more memory for the CLR assemblies?

There is no CLR-specific memory allocation. What memory is available for SQLCLR objects depends on:

• What version of SQL Server you are on. Memory allocation changed starting in SQL Server 2012.
• How much memory is in the server
• If there are other processes running on the server

Quote from the similar thread How do I reduce or add memory allocation for just CLR in SQL Server?


If the response is helpful, please click "Accept Answer" and upvote it, thank you.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NielsBerglund-3563 avatar image
1 Vote"
NielsBerglund-3563 answered NielsBerglund-3563 commented

Hi @JohnWilber-5548!

Couple of questions:

  1. What do you see in the SQL Server Log at the time this error happens?

  2. In your post you say "Obviously, there's a memory usage issue.", what makes you say that?

  3. What does the code look like that that calls the HttpClient class?

As @Cathyji-msft says; you cannot specifically add more memory to SQLCLR.

Niels

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In the SQL Server Log, I see instances of a message similar to "AppDomain 36 (dbname.dbo[runtime].41 created".
Then, it's followed by "AppDomain 36 (dbname.dbo[runtime].41) unloaded".

My understanding is that AppDomains are unloaded due to memory pressure within the CLR memory space, but I may be wrong.

I occasionally see this error message, too, after the ones above:
AppDomain dbname.dbo[runtime].41 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.

John

0 Votes 0 ·

Additionally, in some other research on another issue, I came across this,
http://www.bryancook.net/2008/05/net-garbage-collection-behavior-for.html
which suggests that garbage collection in debug mode is not quite as vigorous as in release mode. I believe that what's running in our environment may have been compiled in debug mode, but I can't be sure because I didn't write it, build it, or release it. I have a theory that memory is not being freed up as quickly as we would like when the CLR function is called many times in quick succession. It seems that when the CLR function is called 50 times, we have no problems, but when it's called 300 times, there are frequent problems.

John

0 Votes 0 ·

The actual code that does the Http call is this:

     // Argument checking here...
     HttpRequestClientWebException client = new HttpRequestClientWebException();
     Response httpResponse = new Response();
        
     if (methodType == MethodType.Get)   // We always call with Gets, as far as I know
     {
         httpResponse = client.Get(uri.Value, EncodingType.Utf8, username, password, headers);
     }
        
     // skipped code for brevity
        
     if (httpResponse.Success)
     {
         JsonResultCollection.Add(new HttpJsonResult(httpResponse.RawResult, null, SqlBoolean.True));
     }
     else
     {
         JsonResultCollection.Add(new HttpJsonResult(httpResponse.RawResult, httpResponse.ErrorResult, SqlBoolean.False));
     }
 `  `return JsonResultCollection;   // It's a table-valued CLR function





0 Votes 0 ·
Show more comments