Hello,
I have a few questions about the behavior of the CLR host within SQL Server 2005. We are using a UDT (call it MyDateTime) created in C# that represents the COM FILETIME type, in order to have single millisecond resolution. MyDateTime values are stored in the database as binary(8), with the UDT itself being used primarily for display and reporting purposes. I am running performance tests using a prototype (written in C# as well) that runs 20 threads which repeatedly call a stored procedure, which accepts two MyDateTime's, and queries a table based on those MyDateTime's binary string representation. After a certain amount of time (depending on the particular system's resources), threads will start to be aborted. Most of the time the reason is "SQL Exception: .NET Framework execution was aborted by escalation policy because of out of memory." Sometimes, eventually the appdomain will be unloaded, and if I restart the prototype, the process starts over. Sometimes, I will have to restart the server before any more CLR processing can occur (no automatic appdomain unload). While the prototype is running, I'll check the MEMORYCLERK_SQLCLR rows in sys.dm_os_memory_clerks, and see the columns for pages and virtual memory ever increasing, until a threshold is hit (on my system, approximately 225 megs of virtual memory committed), resulting in all 20 threads being aborted, one by one, within 30-45 seconds. During that time some of the remaining threads will still have successful calls, while others are aborted.
I understand the necessity for the CLR in SQL Server to monitor and abort threads, in order to preserve the database server itself, as well as the importance of exception handling client-side, but unless the UDT code itself has a leak in it (I'm fairly confident it doesn't), this behavior confuses me. X amount of stored procedure calls (on my system, approximately 65,000 within an hour) can occur before SQL Server runs out of memory, and will constantly abort any thread trying to use the UDT, until it decides to unload the appdomain Is it entirely up to the client to catch any threadaborts and retry those transactions, and is there any way to facilitate or predict if/when the appdomain is going to unload Am I missing something about how garbage collection is functioning within SQL Server, or the CLR itself Even simple CLR code slowly eats up the memory and causes the same results, if enough transactions are made. Does a long running or high transaction system have to anticipate a regular intervention by the escalation policy
Any insight you could give me would be greatly appreciated. Have a good day.
-Dan
P.S. I'm running the September CTP of SQL Server and the Release Candidate of Visual Studio, based on our current development requirements--I will upgrade when I can.
SQL CLR memory management
mswin
20 threads operating on a small UDT doesn't sound like a lot to be hitting OOM. This is in a safe assembly that isn't using static fields or finalizers, right Running ordinary CLR code under moderate load should not be causing OOM exceptions.
A good way to look into this would be to monitor the .NET CLR Memory counters for sqlservr under PerfMon and check % Time in GC, # GC Handles, and Allocated Bytes/Sec. Report back what you find.
More info about the CLR Perf counters and what to look out for is here: http://blogs.msdn.com/maoni/archive/2004/06/03/148029.aspx
aleydro
Feels great to finally tie up this loose end!
It turns out this was an actual bug in the server that resulted in a small memory leak in the case where a UDT is defined as a parameter, but passed in using its string representation.
Example:
proc p_1 @udt MyUdt as...
exec p_1 'MyUdtAsAString'
The good news is that SQLCLR automatically handles the memory leak by unloading the appdomain once it begins to run low on memory, so the primary negative effect is the performance impact of unloading/reloading appdomains more frequently.
This has been fixed in the SP1 Cumulative Hotfix that was just released and is available here (this issue is listed as Bug 636):
http://support.microsoft.com/Default.aspx id=918222
It will also be fixed in SP2
Steven
fdr
Hi Dan,
It should not be necessary to restart the server to regain CLR functionality. Using DBCC FREESYSTEMCACHE should be enough to purge the SQLCLR memory clerk, if it is not the case please let me know and we'll investigate this some more.
About your problematic memory usage issue, it would greatly help if I could see your assembly's code. Would that be possible
Thank you!
-Mat
acs_atchyut
Steven,
I apologize for my delayed response. I have been a bit sidetracked lately. First, the assembly is safe, and uses no static fields or finalizers. I ran perfmon from the start of the server to the appdomain unload. I also monitored some other aspects of CLR Memory (Gen0,1,2 heap size, promoted bytes/sec, etc).
I am seeing that approximately every minute, the gen 2 heap size will generally increase by a few megs. For example, at the beginning, the Gen 2 heap size was 12 bytes. After 20 minutes, 34 megs. After 45 Minutes, 83 megs! At the point of appdomain unload, Gen 2 is at about 142 megs. Up until the last 6 minutes or so (about 1 hour 15 minutes for the entire test), the % time in GC is a fraction of a percent, but then slowly increases (fluctuating with smaller, single digit %'s) to 10, 20, 30, 40% , until it is pinning the CPU at the point of unload.
To make sure this wasn't a client-related issue, I ran a script in Sql Server Management Studio that looped through calls to the stored procedure, while monitoring CLR memory, and I see the same behavior.
So this would seem to imply a memory leak in my assembly I would expect a fairly simple class that only holds a few simple values has no business in Gen 2! If you agree, do you have any suggestions for ways to monitor the assembly itself to monitor where memory is going and what is/isn't scheduled for GC
Thank you for your help, and once again I apologize for my delayed follow-up.
Dan