AppDomain is marked for unload due to memory pressure.

[Livermore@discussions.microsoft.com Robert Livermore]

Hi All

There is an SQL Server which is reporting the following errors during the
busiest times of the day.

Date,Source,Severity,Message
11/26/2008 10:45:29,spid172,Unknown,AppDomain 14 (App.dbo[runtime].13)
created.
11/26/2008 10:45:26,spid1s,Unknown,AppDomain 13 (App.dbo[runtime].12)
unloaded.
11/26/2008 10:45:26,spid1s,Unknown,AppDomain 13 (App.dbo[runtime].12) is
marked for unload due to memory pressure.
11/25/2008 15:01:35,spid152,Unknown,AppDomain 13 (App.dbo[runtime].12)
created.
11/25/2008 15:00:08,spid1s,Unknown,AppDomain 12 (App.dbo[runtime].11)
unloaded.
11/25/2008 15:00:08,spid1s,Unknown,AppDomain 12 (App.dbo[runtime].11) is
marked for unload due to memory pressure.
11/25/2008 14:00:10,spid114,Unknown,AppDomain 12 (App.dbo[runtime].11)
created.
11/25/2008 14:00:09,spid1s,Unknown,AppDomain 11 (App.dbo[runtime].10)
unloaded.
11/25/2008 14:00:09,spid1s,Unknown,AppDomain 11 (App.dbo[runtime].10) is
marked for unload due to memory pressure.
11/25/2008 13:00:15,spid198,Unknown,AppDomain 11 (App.dbo[runtime].10)
created.
11/25/2008 13:00:09,spid1s,Unknown,AppDomain 10 (App.dbo[runtime].9) unloaded.
11/25/2008 13:00:09,spid1s,Unknown,AppDomain 10 (App.dbo[runtime].9) is
marked for unload due to memory pressure.

The Version is patched to SQL Server 2005 sp2 CUM 3.

The closets KB which match the symptoms is
http://support.microsoft.com/default.aspx/kb/928083
(Reject this one because SP2 has been applied. However The CLR routine does
use the connection context to read data from helper table)

http://support.microsoft.com/default.aspx/kb/953497
(Reject this one because there are no tabled value functions used.)

The scalar value function receives set of strings and returns on string. All
objects (DataSets, Connection) supporting IDisposable are surrounded with
“using” statement. This should guarantee object cleaned or put into pools.

My theory is the GC is being starved and cannot keep up with the load or
there is memory leak. Eventually the whole app domain exceeds an max heap
size and the domain is reloaded.

What performance counters, profiler trace options or tools should use to
look for leaking memory, unexpected pinned memory or starved GC threads?

What are the triggers for the SQLOS to reload an AppDomain?

Thanks in advance,
Rob

[Adam Machanic]

32-bit or 64-bit? Have you tried tweaking the -g startup option for
sqlservr.exe? That option configures the mem-to-leave area, which is where
SQLCLR pulls its memory from... It defaults to 256 MB on 32-bit systems, so
it might need a tweak. Also, how are you doing the string concatenation in
your function? Are you using StringBuilder, or the concatenation operator
(+)? The latter could be causing part of your problem if it's what you're
using, as a lot more objects have to be allocated and deallocated than with
StringBuilder...

With regard to perf counters, the .NET counters are the most useful; there
are also a handful of DMVs you can use. Check out Kimberly Tripp's white
paper, which talks about some of this towards the end:

http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm

"Robert Livermore" <Robert Live...@discussions.microsoft.com> wrote in
message news:E7B9F71D-DB83-476D...@microsoft.com...

[Adam Machanic]

Upon re-reading your post I see you're also using DataSets. Can you use
SqlDataReaders instead? Much, much less memory hungry than DataSets.

--
Adam Machanic
http://sqlblog.com

"Robert Livermore" <Robert Live...@discussions.microsoft.com> wrote in
message news:E7B9F71D-DB83-476D...@microsoft.com...