November 14, 2007 at 9:52 am
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.
November 14, 2007 at 10:28 am
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).
😎
November 14, 2007 at 10:34 am
Yes, it's 32-bit version, /PAE enabled, account has access to "lock pages in memory".
December 5, 2007 at 10:55 am
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