Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 SQL Server Memory issue

Author  Topic 

shishirkhandekar
Starting Member

23 Posts
Posted - 2011-02-24 : 09:55:36
Can someone help me figure out how to further investigate memory issues that we are experiencing? Attached is some information (I have more but won't attach it in the thread right now due to it's size) that is relevant.

System:
32-bit SQL Server on Windows 2003, SP2 32-bit
Use /PAE flag but not /3GB flag
64 GB of memory
16 way 2.13 GHz processors
AWE in use, default memory options for SQL Server
Lock pages in memory granted to service account

Errors:
2011-02-23 23:54:45.200 spid94s There is insufficient system memory to run this query.
2011-02-23 23:55:00.220 spid1s AppDomain 391 (MyDatabase.dbo[runtime].438) is marked for unload due to memory pressure.

Found this in the error log during the errors:
Memory Manager
VM Reserved = 1813736 KB
VM Committed = 497408 KB
AWE Allocated = 36012032 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB

2011-02-23 23:54:33.080 spid17s
Memory node Id = 0
VM Reserved = 1809576 KB
VM Committed = 493400 KB
AWE Allocated = 36012032 KB
SinglePage Allocator = 206736 KB
MultiPage Allocator = 76448 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 28136 KB
MultiPage Allocator = 53344 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 1571320 KB
VM Committed = 368920 KB
AWE Allocated = 36012032 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 13768 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLQUERYEXEC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLOPTIMIZER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 46456 KB
MultiPage Allocator = 88 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLUTILITIES (Total)
VM Reserved = 240 KB
VM Committed = 240 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 96 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLSTORENG (Total)
VM Reserved = 3776 KB
VM Committed = 3776 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 12400 KB
MultiPage Allocator = 280 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLCONNECTIONPOOL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 17872 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLCLR (Total)
VM Reserved = 135872 KB
VM Committed = 23008 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1472 KB
MultiPage Allocator = 1480 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLSERVICEBROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 112 KB
MultiPage Allocator = 192 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLHTTP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SNI (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 112 KB
MultiPage Allocator = 16 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_FULLTEXT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLXP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_BHF (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 7080 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLQERESERVATIONS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1024 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_HOST (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 24 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SOSNODE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 35224 KB
MultiPage Allocator = 6984 KB

2011-02-23 23:54:33.080 spid17s
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 24 KB
MultiPage Allocator = 0 KB

2011-02-23 23:54:33.080 spid17s Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0

2011-02-23 23:54:33.080 spid17s Global Memory Objects:
Resource= 1981
Locks= 326
SE Schema Mgr= 4207
SQLCache= 51
Replication= 2
ServerGlobal= 27
XPGlobal= 2
Xact= 115
SETLS= 16
DatasetMemObjs= 32
SubpDescPmos= 16
SortTables= 2

2011-02-23 23:54:33.080 spid17s Query Memory Objects: Grants=1 Waiting=0 Maximum=84603 Available=84475 Limit=84603

2011-02-23 23:54:33.080 spid17s Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=89055

2011-02-23 23:54:33.080 spid17s Small Query Memory Objects: Grants=0 Waiting=0 Maximum=4488 Available=4488 Limit=4488

2011-02-23 23:54:33.080 spid17s
Optimization Queue
Overall Memory = 840499200 Target Memory = 743694336 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
2011-02-23 23:54:33.080 spid17s
MEMORYBROKER_FOR_CACHE
Allocations = 7217
Rate = 758
Target Allocations = 80131
Future Allocations = 0
Last Notification = GROW

2011-02-23 23:54:33.080 spid17s
MEMORYBROKER_FOR_STEAL
Allocations = 18642
Rate = -15
Target Allocations = 90783
Future Allocations = 0
Last Notification = GROW

2011-02-23 23:54:33.080 spid17s
MEMORYBROKER_FOR_RESERVE
Allocations = 256
Rate = 1087
Target Allocations = 94998
Future Allocations = 22842
Last Notification = GROW

Thanks
SK
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site