sqlserver12345 (10/12/2011)
24:11.4spid1sAppDomain 65 (SMS_N07.dbo[runtime].68) is marked for unload due to memory pressure.24:11.4spid1sAppDomain 65 (SMS_N07.dbo[runtime].68) unloaded.
24:14.6spid65AppDomain 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)