August 11, 2011 at 9:51 am
**SQL 2005 - SP3 - Enterprise Edition with recent Memory upgrade from 32 GB to 64 GB
Strangely we are getting "INSUFFIENT MEMORY ERROR" and SQL Server restarts at times automatically ....adding memory shouldn't be the cause of this but I am thinking something to do with the Service Broker or ?
Please advice!!
2011-08-11 09:05:01.25 spid3s Warning: The available page file space has dropped below 4Mb.2011-08-11 09:05:40.26 spid101 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
2011-08-11 09:05:40.48 spid101
Memory Manager
VM Reserved = 70424568 KB
VM Committed = 63633284 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
2011-08-11 09:05:40.48 spid101
Memory node Id = 0
VM Reserved = 11328 KB
VM Committed = 30178504 KB
AWE Allocated = 0 KB
SinglePage Allocator = 9004840 KB
MultiPage Allocator = 9840 KB
2011-08-11 09:05:40.48 spid101
Memory node Id = 1
VM Reserved = 70407480 KB
VM Committed = 33449104 KB
AWE Allocated = 0 KB
SinglePage Allocator = 9004840 KB
MultiPage Allocator = 2769464 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLGENERAL (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 57040 KB
MultiPage Allocator = 5376 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLGENERAL (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1205096 KB
MultiPage Allocator = 80 KB
2011-08-11 09:05:40.48 spid101
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 = 1262136 KB
MultiPage Allocator = 5456 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLBUFFERPOOL (node 0)
VM Reserved = 67190784 KB
VM Committed = 30233088 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 520 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLBUFFERPOOL (node 1)
VM Reserved = 0 KB
VM Committed = 30167552 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 67190784 KB
VM Committed = 60400640 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 520 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLQUERYEXEC (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 688 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLQUERYEXEC (node 1)
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-08-11 09:05:40.48 spid101
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 = 696 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLOPTIMIZER (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 7392 KB
MultiPage Allocator = 152 KB
2011-08-11 09:05:40.48 spid101
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 = 7392 KB
MultiPage Allocator = 152 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLUTILITIES (node 0)
VM Reserved = 120 KB
VM Committed = 120 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 472 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLUTILITIES (Total)
VM Reserved = 120 KB
VM Committed = 120 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 472 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLSTORENG (node 0)
VM Reserved = 16320 KB
VM Committed = 16320 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1824 KB
MultiPage Allocator = 32 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLSTORENG (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4184 KB
MultiPage Allocator = 1632 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLSTORENG (Total)
VM Reserved = 16320 KB
VM Committed = 16320 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6008 KB
MultiPage Allocator = 1664 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLCONNECTIONPOOL (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 584 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLCONNECTIONPOOL (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 552 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
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 = 1136 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLCLR (node 0)
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-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLCLR (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-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLSERVICEBROKER (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 376 KB
MultiPage Allocator = 320 KB
2011-08-11 09:05:40.48 spid101
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 = 376 KB
MultiPage Allocator = 320 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLHTTP (node 1)
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-08-11 09:05:40.48 spid101
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-08-11 09:05:40.48 spid101
MEMORYCLERK_SNI (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 64 KB
MultiPage Allocator = 16 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SNI (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 56 KB
MultiPage Allocator = 16 KB
2011-08-11 09:05:40.48 spid101
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 = 120 KB
MultiPage Allocator = 32 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_FULLTEXT (node 1)
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-08-11 09:05:40.48 spid101
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-08-11 09:05:40.48 spid101
MEMORYCLERK_SQLXP (node 0)
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-08-11 09:05:40.48 spid101
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-08-11 09:05:40.48 spid101
MEMORYCLERK_BHF (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 408 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_BHF (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 120 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
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 = 528 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_HOST (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 120 KB
MultiPage Allocator = 16 KB
2011-08-11 09:05:40.48 spid101
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 = 120 KB
MultiPage Allocator = 16 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SOSNODE (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 107656 KB
MultiPage Allocator = 12328 KB
2011-08-11 09:05:40.48 spid101
MEMORYCLERK_SOSNODE (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 123424 KB
MultiPage Allocator = 7776 KB
2011-08-11 09:05:40.48 spid101
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 = 231080 KB
MultiPage Allocator = 20104 KB
2011-08-11 09:05:40.49 spid101
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 48 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
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 = 48 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_OBJCP (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 742680 KB
MultiPage Allocator = 26560 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_OBJCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 742680 KB
MultiPage Allocator = 26560 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_SQLCP (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6562112 KB
MultiPage Allocator = 2723288 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_SQLCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6562112 KB
MultiPage Allocator = 2723288 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_PHDR (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 53360 KB
MultiPage Allocator = 160 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_PHDR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 53360 KB
MultiPage Allocator = 160 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_XPROC (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 192 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_XPROC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 192 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_TEMPTABLES (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 208 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_TEMPTABLES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 208 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_NOTIF (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_NOTIF (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-08-11 09:05:40.49 spid101
CACHESTORE_VIEWDEFINITIONS (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_VIEWDEFINITIONS (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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBTYPE (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBTYPE (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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBELEMENT (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBELEMENT (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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBATTRIBUTE (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_XMLDBATTRIBUTE (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-08-11 09:05:40.49 spid101
CACHESTORE_STACKFRAMES (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_STACKFRAMES (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_STACKFRAMES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 16 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_BROKERTBLACS (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 472 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_BROKERTBLACS (node 1)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERTBLACS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 480 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_BROKERKEK (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERKEK (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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERDSH (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERDSH (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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERUSERCERTLOOKUP (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERUSERCERTLOOKUP (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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERRSB (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERRSB (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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERREADONLY (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 88 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_BROKERREADONLY (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 88 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_BROKERTO (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_BROKERTO (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-08-11 09:05:40.49 spid101
CACHESTORE_EVENTS (node 0)
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-08-11 09:05:40.49 spid101
CACHESTORE_EVENTS (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-08-11 09:05:40.49 spid101
CACHESTORE_SYSTEMROWSET (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4128 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
CACHESTORE_SYSTEMROWSET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4128 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_SCHEMAMGR (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 10912 KB
MultiPage Allocator = 144 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_SCHEMAMGR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 10912 KB
MultiPage Allocator = 144 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_DBMETADATA (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 19040 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_DBMETADATA (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 19040 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_TOKENPERM (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 2712 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_TOKENPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 2712 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_OBJPERM (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6792 KB
MultiPage Allocator = 32 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_OBJPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6792 KB
MultiPage Allocator = 32 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_SXC (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 184 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_SXC (node 1)
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 = 0 KB
2011-08-11 09:05:40.49 spid101
USERSTORE_SXC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 296 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LBSS (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 128 KB
MultiPage Allocator = 480 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LBSS (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 128 KB
MultiPage Allocator = 64 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LBSS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 256 KB
MultiPage Allocator = 544 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_SNI_PACKET (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1080 KB
MultiPage Allocator = 48 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_SNI_PACKET (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 800 KB
MultiPage Allocator = 48 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_SNI_PACKET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1880 KB
MultiPage Allocator = 96 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_SERVICE_BROKER (node 0)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 288 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_SERVICE_BROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 288 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LOCK_MANAGER (node 0)
VM Reserved = 262144 KB
VM Committed = 262144 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 24000 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LOCK_MANAGER (node 1)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 64720 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101
OBJECTSTORE_LOCK_MANAGER (Total)
VM Reserved = 262144 KB
VM Committed = 262144 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 88720 KB
MultiPage Allocator = 0 KB
2011-08-11 09:05:40.49 spid101 Buffer Distribution: Stolen=188873 Free=25749 Cached=936646
Database (clean)=6028431 Database (dirty)=244285
I/O=0, Latched=16
2011-08-11 09:05:40.49 spid101 Buffer Counts: Committed=7424000 Target=7424000 Hashed=6272732
Internal Reservation=1484800 External Reservation=0
Stolen Potential=5927865
Min Free=512 Visible=7424000
Available Paging File=5033984
2011-08-11 09:05:40.49 spid101 Procedure Cache: TotalProcs=108283 TotalPages=1263591 InUsePages=2383
2011-08-11 09:05:40.49 spid101 Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
2011-08-11 09:05:40.49 spid101 Global Memory Objects:
Resource=522
Locks=11093
SE Schema Mgr=1381
SQLCache=157422
Replication=2
ServerGlobal=58
XPGlobal=2
Xact=205
SETLS=16
DatasetMemObjs=32
SubpDescPmos=19
SortTables=2
2011-08-11 09:05:40.49 spid101 Query Memory Objects: Grants=0 Waiting=0 Maximum=5544295 Available=5544295 Limit=5544295
2011-08-11 09:05:40.49 spid101 Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=5557095
2011-08-11 09:05:40.49 spid101 Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800
2011-08-11 09:05:40.49 spid101
Optimization Queue
Overall Memory = 48748953600 Target Memory = 38702940160 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
2011-08-11 09:05:40.49 spid101
Small Gateway
Configured Units = 64 Available Units = 63 Acquires = 1
Waiters = 0 Threshold Factor = 380000 Threshold = 380000
2011-08-11 09:05:40.49 spid101
Medium Gateway
Configured Units = 16 Available Units = 16 Acquires = 0
Waiters = 0 Threshold Factor = 12 Threshold = 3225245013
2011-08-11 09:05:40.49 spid101
Big Gateway
Configured Units = 1 Available Units = 1 Acquires = 0
Waiters = 0 Threshold Factor = 8 Threshold = -1
2011-08-11 09:05:40.49 spid101
MEMORYBROKER_FOR_CACHE
Allocations = 936657
Rate = 145
Target Allocations = 5472863
Future Allocations = 0
Last Notification = GROW
2011-08-11 09:05:40.49 spid101
MEMORYBROKER_FOR_STEAL
Allocations = 188256
Rate = 163
Target Allocations = 4724480
Future Allocations = 0
Last Notification = GROW
2011-08-11 09:05:40.49 spid101
MEMORYBROKER_FOR_RESERVE
Allocations = 0
Rate = -89
Target Allocations = 5927579
Future Allocations = 1391518
Last Notification = GROW
2011-08-11 09:05:40.49 spid101 Error: 701, Severity: 17, State: 123.
2011-08-11 09:05:40.49 spid101 There is insufficient system memory to run this query.
August 11, 2011 at 9:53 am
Not much to say and no I don't expect anybody to read all of this for free.
You don't have enough ram to run the query to make the query need more ressources or add ram.
August 11, 2011 at 10:01 am
Appreciate your quick and 'free' response.
32 GB was good enough but apparently 64 GB is not 🙂
anyways, as you can tell I am new to asking at forums for help, the aim was to see if anyone has gone through a similar experience upon memory upgrade (without the workload changes).
August 11, 2011 at 10:06 am
WasimAli (8/11/2011)
Appreciate your quick and 'free' response.32 GB was good enough but apparently 64 GB is not 🙂
anyways, as you can tell I am new to asking at forums for help, the aim was to see if anyone has gone through a similar experience upon memory upgrade (without the workload changes).
That's more usefull to help.
Does this occur often or is it a one time deal?
What querie(s) are busting the limit?
August 11, 2011 at 10:10 am
What else changed besides the ram?
(Beat your devs / dbas untill they tell you) 😉
August 11, 2011 at 10:21 am
Seriously ..nothing has changed with the exception of RAM upgrade
hey don't ask me to beat myself -- I am supposedly the DBA for this 🙂
I was hoping to see a better performance given that the RAM was doubled, but certainly not this that the service restarts itself stating insufficient memory.
Do any of these play a key role in tweaking how memory is being used..any input?
LOCK PAGES IN MEMORY --
select state,COUNT(*) from sys.conversation_endpoints group by state --
August 11, 2011 at 10:25 am
I'd only be guessing at this point. Little too far out of my expertise to make recommendations.
I'll step aside for now.
August 11, 2011 at 10:53 am
that's all i can get for free i guess 🙂
August 11, 2011 at 11:13 am
WasimAli (8/11/2011)
that's all i can get for free i guess 🙂
No we all give a lot of free stuff. That implies that we know the answer which is not the case for me here.
What we won't do is something like here's a proc with 5000 lines of code and 150 queries. Why is it running slow...
Normal debug time for that is 40-80 hours. Not doable online.
and where you where getting close to with your original post.
This is more a of "try these 5-10 things" back and forth which is doable on a forum.
August 11, 2011 at 11:29 am
sorry..it was meant to be a joke.
I understand that I am not providing enough information to get proper feedback, but I am looking at general guidelines for using 64 GB Memory on SQL 2005 Enterprise 64 bit server.
you know ...try these 5-10 things that you very rightly put in.
August 11, 2011 at 11:33 am
WasimAli (8/11/2011)
sorry..it was meant to be a joke.I understand that I am not providing enough information to get proper feedback, but I am looking at general guidelines for using 64 GB Memory on SQL 2005 Enterprise 64 bit server.
you know ...try these 5-10 things that you very rightly put in.
Back to 3000 line proc. Books SERIES have been written on the subject.
You might want to hit google with sql 2005 best practices memory or setup... while you wait for us.
August 11, 2011 at 12:07 pm
You're in luck, very similar thread to yours going on at the moment. You can follow along but please don't hijack it.
http://www.sqlservercentral.com/Forums/Topic1158474-1550-1.aspx
August 11, 2011 at 12:10 pm
First things I'd do:
Ensure SQL's max memory is set. 58-60GB for 64GB total assuming that it's a dedicated server.
Check what other than SQL is using the memory.
Oh, and make sure you're on SP3 or above, preferably SP4. There were some fun memory bugs on RTM that resulted in worse performance the more memory you had.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2011 at 9:31 am
Thanks a lot to all those who responded with their helpful responses and I am also following the other post which although related to 2008 -- but nevertheless is a good place to keep up with the options I can try.
I am trying to get SP4 installed, but as you can understand, it has to go through the whole round of testing etc before it can make it to production.
I realized that after I changed the memory from MAX 58GB to MAX 55GB it appears to be more stable now ..knock on wood (*Maybe, I will just keep lowering it down in the mean time if it doesn't help - a stable system is better than a marginally better performing one with a chance that nobody can get to it during peak hours).
August 15, 2011 at 11:42 am
Additional information obtained;
PAGE FILE Was removed by my server counterpart with the good intention that it should help boost performance (given that we upgraded from 32 to 64 GB recently).
looks like it is backfiring in this case..
Any input on the "PAGE" file setup or the lack of it w.r.to SQL
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply