SQL CLR Memory Management

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.

• Memory Management (fixed Memory, AWE)
• SQL Server 2005 Memory Management
• SQL Server 2005 Memory Management
• 2005 Memory Management Questions
• Memory Management In A Clustered SQL2000 Environment
• SQL Dynamic Memory Management And Low Free Buffers/
• Speed, Memory Usage Question For Photo Management
• While Opening Management Studio I Am Getting Error--Attempted To Read Or Protected Memory.
• Attempted To Read Or Write Protected Memory. This Is Often An Indication That Other Memory Is Corrupt. (Microsoft Visual Studio)
• AWE/Lock Page In Memory Drawbacks? Min/Max Settings, Memory Issues
• SQL Not Utilizing Available 2gig Memory Yet Giving Memory Timeouts
• Memory Leak...How To Get Memory Usage For Each Connections?
• Dynamic Memory Allocation??? (releasing Memory)
• Memory Issues, SSIS Package Out Of Memory Help
• Sql Server 2000 Using Less Memory After More Memory Added
• Would Max Memory Including SSIS And SSAS Memory
• Management Studion Express Alongside Full-blown Management Studio?
• SQL Server Management Express Studio Management Tools
• How Do You Troubleshoot Memory Leak Issues? What Tools Can Use To Diagnose Memory Leak Problems? In MS SQL ?
• Performance Issues Total Server Memory Vs Target Server Memory
• Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)
• Is SQL 2005 Memory Limitation &&<= OS Memory Limitation?
• Got Enough Memory???
• Memory
• Memory Pb
• Memory In SQL
• SQL And Memory
• Memory
• Memory
• Memory
• Max Memory On SQL 6.5
• Memory
• 98 % Of Memory !
• Using Memory Over 2 Gig Without AWE
• Is SQL A Memory Hog?
• Sql 7 Memory Only Using Up To 2GB RAM
• How Much Memory To Buy....
• Regarding Memory
• Memory
• Out Of Memory
• Does SQL Use AWE Memory Whenever Possible?
• 64 Bit Memory And AWE ?
• Enough Memory?
• Memory
• Max Memory Used
• XML And Memory