October 12, 2011 at 1:47 pm
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
October 12, 2011 at 10:20 pm
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)
October 13, 2011 at 5:19 pm
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?
October 13, 2011 at 7:19 pm
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...
October 18, 2011 at 12:56 pm
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 ?
October 18, 2011 at 1:36 pm
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?
February 14, 2014 at 9:19 am
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
February 21, 2014 at 2:08 pm
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# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply