November 22, 2011 at 7:06 am
We have a server (SQL 2008 SP2) that is going sideways every 10 - 14 days. When the problem starts, this entry shows up in the event log:
"There is insufficient system memory in resource pool 'internal' to run this query."
I've googled around and found other posts on this forum regarding the error and I've read some of the fixes (although I haven't tried any of them yet) and I've read the Microsoft kb article. Below is the output from the dbcc memorystatus as the errors were being logged. I'm convinced there is a memory leak in an extended dll that is repeatedly being executed. Is there something in the memorystatus output that will help confirm or rule this out?
Memory Manager KB
---------------------------------------- -----------
VM Reserved 2678008
VM Committed 1271416
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 2675192
VM Committed 1268712
AWE Allocated 0
MultiPage Allocator 15552
SinglePage Allocator 23744
(5 row(s) affected)
Memory node Id = 32 KB
---------------------------------------- -----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 23744
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1560
MultiPage Allocator 2296
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 2647680
VM Committed 1242248
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 744
(7 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 152
MultiPage Allocator 88
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 120
VM Committed 120
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 120
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 1920
VM Committed 1920
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2096
MultiPage Allocator 2936
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1328
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 344
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 104
MultiPage Allocator 32
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 96
MultiPage Allocator 64
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2848
MultiPage Allocator 7280
(7 row(s) affected)
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2864
MultiPage Allocator 8800
(7 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 192
SM Committed 192
SinglePage Allocator 0
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 4096
MultiPage Allocator 736
(7 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 184
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 160
MultiPage Allocator 200
(7 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 80
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 192
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 112
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2576
MultiPage Allocator 16
(7 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 296
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2024
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 720
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 408
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 128
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1496
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1496
MultiPage Allocator 112
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2152
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 4096
VM Committed 4096
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2176
MultiPage Allocator 0
(7 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Committed 153600
Target 153600
Database 147750
Dirty 1460
In IO 0
Latched 0
Free 2880
Stolen 2970
Reserved 0
Visible 153600
Stolen Potential 142950
Limiting Factor 17
Last OOM Factor 0
Last OS Error 0
Page Life Expectancy 101535
(15 row(s) affected)
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 1058766848
Available Virtual Memory 10919936
Available Paging File 7715893248
Working Set 1627680768
Percent of Committed Memory in WS 100
Page Faults 9587938
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 1
(10 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 2
TotalPages 71
InUsePages 22
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 187
Locks 272
XDES 85
SETLS 7
SE Dataset Allocators 14
SubpDesc Allocators 7
SE SchemaManager 159
SE Column Metadata Cache 164
SQLCache 35
Replication 2
ServerGlobal 26
XP Global 2
SortTables 2
(13 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 109654
Current Max 109654
Future Max 109654
Physical Max 109654
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 5771
Current Max 5771
Future Max 5771
(5 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- -----------
Overall Memory 1008599040
Target Memory 959545344
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 28
Available Units 28
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 7
Available Units 7
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager Pages
---------------------------------------- -----------
Reserved Current 0
Reserved Limit 142950
(2 row(s) affected)
Memory Pool (internal) Pages
---------------------------------------- -----------
Allocations 2970
Predicted 30392
Private Target 0
Private Limit 0
Total Target 145920
Total Limit 145920
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- -----------
Allocations 1375
Rate -615
Target Allocations 116903
Future Allocations 0
Overall 123120
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- -----------
Allocations 1595
Rate 9
Target Allocations 117132
Future Allocations 0
Overall 123120
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 123120
Future Allocations 27413
Overall 123120
Last Notification 1
(6 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 22, 2011 at 7:22 am
sqlpadawan_1 (11/22/2011)
We have a server (SQL 2008 SP2) that is going sideways every 10 - 14 days. When the problem starts, this entry shows up in the event log:"There is insufficient system memory in resource pool 'internal' to run this query."
Please check: http://support.microsoft.com/kb/982854 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 22, 2011 at 7:32 am
Thanks, I've read the kb article. We're not using FTS. Reading down through the doc Microsoft first fixed the issue in SP1. Since we're at SP2 we should have the fix installed anyways, right?
November 22, 2011 at 8:00 am
What might be the case is since this was fixed in CU9 for SP1 that fix may not have made it into the release of SP2. They show a CU after SP2 that has the fix. That leads me to believe that the fix is not in SP2.
November 22, 2011 at 9:42 am
Thanks for the input. I will see if we can apply SP3 or if they want to limp through the holidays.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply