Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

App Domain errors Expand / Collapse
Author
Message
Posted Wednesday, October 12, 2011 1:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) is marked for unload due to memory pressure.
24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) unloaded.
24:14.6 spid65 AppDomain 66 (SMS_N07.dbo[runtime].69) created.

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb
from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'
Result:
179076
0


Keep getting these.Any thoughts.
We have a 64bit OS.
How do I identify the initial -g switch value.Where do I look for this default value.Is there t-sql which can identify the startup parameters?

Thanks

Post #1189421
Posted Wednesday, October 12, 2011 10:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:26 PM
Points: 316, Visits: 1,497
sqlserver12345 (10/12/2011)
24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) is marked for unload due to memory pressure.
24:11.4 spid1s AppDomain 65 (SMS_N07.dbo[runtime].68) unloaded.
24:14.6 spid65 AppDomain 66 (SMS_N07.dbo[runtime].69) created.

select single_pages_kb + multi_pages_kb + virtual_memory_committed_kb
from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLCLR'
Result:
179076
0


Keep getting these.Any thoughts.
We have a 64bit OS.
How do I identify the initial -g switch value.Where do I look for this default value.Is there t-sql which can identify the startup parameters?

Thanks



You mentioned you're on a 64 bit OS, therefore, i'm assuming you're running 64 bit sql server.

How much memory is on the box, what are your min/max memory settings, and does anything else run on the server?

On 32 bit sql server, -g could help, as it ensures you have a big enough contigous block of virtual address space outside the buffer pool. ( aka, MemToLeave). On 64 bit, the address space is huge, so running out of virtual address space is not likely. You'll run out of physical memory first. so, -G

two possibilities:
1. You have a memory leak in your clr code that causes it to use more and more memory. If it get's too large, sql recycles the app domain.

2. Your sql server memory settings are too high. The Min/Max settings just apply to the buffer pool, CLR uses memory outside of the buffer pool.

Hypothetical example: 32 GB ram, min/max server memory (buffer pool size) set to 30 GB, lock pages in memory is set. this leaves 2 GB for the OS, other applications, and the non-buffer pool memory of sql server (including the clr app domains). External memory pressure occurs (maybe ssis package fires up), the OS notifies sql, sql can't reduce the size of the buffer pool below min, but it can free some memory by unloading app domains. A few minutes after unloading the app domain, a clr function is called, so the app domain is created again.

Try lowering the min/max settings in 500 MB chunks until the clr app domain recycles go away.

You're on the right track as far as the memory clerks go. Watch them over time. Does the SQLCLR clerk keep rising higher and higher until getting killed? Also consider watching the other non-buffer pool memory clerks for any abnormally large memory allocations.

Fire up VMMAP from sysinternals. How much of each kind of memory is sql server commiting? (AWE, image, etc)





Post #1189554
Posted Thursday, October 13, 2011 5:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
Min memory:0 MB
Max memory:2147483647 MB

We have 8Gb RAM on the computer

Should I decrease the max memory to 7GB ?
If i choose to do this how I do it.Do I have to change it to 7GB and ten reboot the server?
Can I change the max value without rebooting the server?
Post #1190250
Posted Thursday, October 13, 2011 7:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:26 PM
Points: 316, Visits: 1,497
You can change it right from ssms. No restart or reboot is required. take the current size of your buffer pool, subtract 200 mb, and try that as the size for max mem. Keep bumping it down until the problems go away. if you have to reduce it to under 6gb, then something fishy is going on...

Post #1190266
Posted Tuesday, October 18, 2011 12:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 5:09 PM
Points: 178, Visits: 579
Would giving "the sql server service account the right to prevent the operating system from paging its memory to disk" get rid of this error ?
i.e
Click Start.
2. Go to Administrative tools | Local Security Policy.
3. Click Local Policies, and then click User Rights
Assignment.
4. Scroll down to Lock pages in memory, and double-click
(See Figure 11).
5. Click Add User or Group, and enter the SQL Server
service account name.

Would this help resolve my problem ?
Post #1192431
Posted Tuesday, October 18, 2011 1:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:26 PM
Points: 316, Visits: 1,497
No, that setting only affects the buffer pool memory, not the memory used for the CLR. If you do enable lock pages in memory, (which I'm not recommending as it wouldn't help) then you must set max memory.

Have you set max memory yet?
Post #1192453
Posted Friday, February 14, 2014 9:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 12:01 PM
Points: 3, Visits: 10
Spring Town -
Thanks for this great explanation. I have a question...
You write "You're on the right track as far as the memory clerks go. Watch them over time. Does the SQLCLR clerk keep rising higher and higher until getting killed? Also consider watching the other non-buffer pool memory clerks for any abnormally large memory allocations."

so if that indeed does happen, where the memory usage goes higher and higher until it gets killed, what does that mean exactly? Can we presume we have a leak? We're running a CLR assembly that when run, the clerk keeps rising. When we stop running it, the clerk remains steady. The clerk doesnt decrease until a restart or the app domain error occurs. Is there a way in the code to clean up the memory space?

Thanks
Post #1541646
Posted Friday, February 21, 2014 2:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:01 PM
Points: 342, Visits: 1,781
dlevin (2/14/2014)
Spring Town -
Thanks for this great explanation. I have a question...
You write "You're on the right track as far as the memory clerks go. Watch them over time. Does the SQLCLR clerk keep rising higher and higher until getting killed? Also consider watching the other non-buffer pool memory clerks for any abnormally large memory allocations."

so if that indeed does happen, where the memory usage goes higher and higher until it gets killed, what does that mean exactly? Can we presume we have a leak? We're running a CLR assembly that when run, the clerk keeps rising. When we stop running it, the clerk remains steady. The clerk doesnt decrease until a restart or the app domain error occurs. Is there a way in the code to clean up the memory space?

Thanks


Sounds like a memory leak. I am not sure of all the ways that could happen in .Net, but I do know that if you are not cleaning up managed resources then it would have that effect as the references to those objects will get orphaned when the process ends. If that happens then the objects can't be disposed and garbage collection will never pick them up.

What is your code doing? Are you accessing any external resources or streams? Are you using any SqlConnection or SqlDataReader classses, or anything like that? If so, all of those references need to be closed (i.e. call the .Close() method), typically in a finally block (just in case there is an exception, the cleanup still happens). If they are not closed then the resources stay allocated and that will take up more and more memory. And if not using a finally block, or at least repeating the close statement(s) in a catch block and errors occur, then that would have the same effect.

Does this make sense and possibly fit your situation? If not, can you please share a little more about what your code is doing?

Take care,
Solomon.





SQL# - http://www.SQLsharp.com/
Post #1544188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse