AppDomain unloads due to memory pressure

  • We deployed a new SQL Server 2005 production server 6 weeks ago. This

    server has 8 GB memory and is configured with AWE enabled, 6656 MB max

    server memory and /3GB switch.

    Twice for the last 2 weeks we had a situation when CPU time went up

    and all users reported a significant system slowdown.

    Every 30 seconds these messages were logged in the error log:

    AppDomain 29 (master.dbo[runtime].28) is marked for unload due to

    memory pressure.

    AppDomain 29 (master.dbo[runtime].28) unloaded.

    AppDomain 30 (master.dbo[runtime].29) created.

    sys.dm_exec_cached_plans view had only few records, so I assume the

    procedure cache was flushed with every AppDomain unload.

    This is the memory snapshot at the time:

    Memory Manager KB

    ------------------------------ --------------------

    VM Reserved 2802932

    VM Committed 141204

    AWE Allocated 6821176

    Reserved Memory 1024

    Reserved Memory In Use 0

    (5 row(s) affected)

    Memory node Id = 0 KB

    ------------------------------ --------------------

    VM Reserved 2798772

    VM Committed 137196

    AWE Allocated 6821176

    MultiPage Allocator 33384

    SinglePage Allocator 64904

    MEMORYCLERK_SQLBUFFERPOOL (Total) KB

    ---------------------------------------- --------------------

    VM Reserved 2612332

    VM Committed 69032

    AWE Allocated 6821176

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 2040

    MEMORYCLERK_SQLCLR (Total) KB

    ---------------------------------------- --------------------

    VM Reserved 133760

    VM Committed 19572

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 1280

    MultiPage Allocator 1488

    Buffer Distribution Buffers

    ------------------------------ -----------

    Stolen 4568

    Free 40186

    Cached 3545

    Database (clean) 699081

    Database (dirty) 104587

    I/O 0

    Latched 1

    (7 row(s) affected)

    Buffer Counts Buffers

    ------------------------------ --------------------

    Committed 851968

    Target 851968

    Hashed 803669

    Stolen Potential 293455

    External Reservation 0

    Min Free 1872

    Visible 317440

    Available Paging File 578065

    (8 row(s) affected)

    Procedure Cache Value

    ------------------------------ -----------

    TotalProcs 7

    TotalPages 92

    InUsePages 42

    This is the memory snapshot during the regular load:

    Memory Manager KB

    ------------------------------ --------------------

    VM Reserved 2795508

    VM Committed 130708

    AWE Allocated 6815904

    Reserved Memory 1024

    Reserved Memory In Use 0

    (5 row(s) affected)

    Memory node Id = 0 KB

    ------------------------------ --------------------

    VM Reserved 2791348

    VM Committed 126700

    AWE Allocated 6815904

    MultiPage Allocator 21296

    SinglePage Allocator 1506648

    MEMORYCLERK_SQLBUFFERPOOL (Total) KB

    ---------------------------------------- --------------------

    VM Reserved 2620524

    VM Committed 69032

    AWE Allocated 6815904

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 0

    MultiPage Allocator 2040

    MEMORYCLERK_SQLCLR (Total) KB

    ---------------------------------------- --------------------

    VM Reserved 135936

    VM Committed 23900

    AWE Allocated 0

    SM Reserved 0

    SM Commited 0

    SinglePage Allocator 880

    MultiPage Allocator 1232

    Buffer Distribution Buffers

    ------------------------------ -----------

    Stolen 8073

    Free 11148

    Cached 180258

    Database (clean) 627780

    Database (dirty) 24708

    I/O 0

    Latched 1

    (7 row(s) affected)

    Buffer Counts Buffers

    ------------------------------ --------------------

    Committed 851968

    Target 851968

    Hashed 652489

    Stolen Potential 114209

    External Reservation 0

    Min Free 1008

    Visible 318464

    Available Paging File 581447

    (8 row(s) affected)

    Procedure Cache Value

    ------------------------------ -----------

    TotalProcs 29506

    TotalPages 176205

    InUsePages 2795

    Both times the server reboot has fixed the problem.

    I'm just wondering if this is an indication of CLR related memory

    leaking or the problem is with SQL Server 2005 memory allocation/

    management that forces AppDomain unloads?

    Do I still need to reboot the server in order to fix it or I can just

    run DBCC FREESYSTEMCACHE?

    Is there a way to prevent this from happening?

    Thanks.

  • I am making the assumption you are running the 32-bit version of SQL Server 2005. With AWE enabled, you may need to remove the 3GB switch and possible need to add /PAE switch. Also be sure the account running SQL Server has the priv "lock pages in memory" (or something like that, you may want to check BOL to be sure as I am working of the top of my head).

    😎

  • Yes, it's 32-bit version, /PAE enabled, account has access to "lock pages in memory".

  • It happens once in 3 days now during the pick hours. DBCC FREESYSTEMCACHE doesn't help at all, only reboot.

    Opened a ticket with Microsoft but they have no idea what's happening.

    Asked me to run PSSDiag and send a result to them.

    These are the performance counters related to memory when the issue

    occurs:

    Memory: Available Bytes: 602,260,000

    Memory: Pages/sec: 0

    Process: Working Set Total: 778,264,576

    Process: Working Set SQL Server: 262,144,000

    SQL Server: Buffer Manager: Buffer Cache Hit Ratio: 99.851

    SQL Server: Buffer Manager: Total Pages: 851,968

    SQL Server: Memory Manager: Total Server Memory (KB): 6,815,744

    MEMORYCLERK_SQLCLR (Total) KB

    ---------------------------------------- --------------------

    VM Reserved 133760

    VM Committed 19572

    There is no any other processes running on the server at the time.

    Don't see any memory pressure.

    This is really frustrating since it was running on a less powerful

    hardware with 3GB RAM with SQL Server 2000 just 2 months ago with no

    issues.

    I really need help with this one.

    Thanks,

    Vlad

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply