September 22, 2009 at 4:06 am
Hi friends
Recently users are getting the error "insufficient memory to run this query".
My server is having 16 GB memory. We have enabled AWE and configured the instance to use 4GB max.
Please tell me if any more information is needed.
following is the details found in the error log
09/22/2009 15:39:04,spid185,Unknown,MEMORYBROKER_FOR_RESERVE Allocations = 2370 Rate = 370 Target Allocations = 9284 Future Allocations = 33074 Last Notification = STABLE
09/22/2009 15:39:04,spid185,Unknown,MEMORYBROKER_FOR_STEAL Allocations = 8479 Rate = -141 Target Allocations = 9284 Future Allocations = 0 Last Notification = STABLE
09/22/2009 15:39:04,spid185,Unknown,MEMORYBROKER_FOR_CACHE Allocations = 167262 Rate = 7336 Target Allocations = 120074 Future Allocations = 0 Last Notification = SHRINK
09/22/2009 15:39:04,spid185,Unknown,Big Gateway Configured Units = 1 Available Units = 1 Acquires = 0 Waiters = 0 Threshold Factor = 8 Threshold = -1
09/22/2009 15:39:04,spid185,Unknown,Medium Gateway Configured Units = 8 Available Units = 8 Acquires = 0 Waiters = 0 Threshold Factor = 12 Threshold = -1
09/22/2009 15:39:04,spid185,Unknown,Small Gateway Configured Units = 32 Available Units = 32 Acquires = 0 Waiters = 0 Threshold Factor = 250000 Threshold = 250000
09/22/2009 15:39:04,spid185,Unknown,Optimization Queue Overall Memory = 1216954368 Target Memory = 76054528 Last Notification = STABLE Timeout = 6 Early Termination Factor = 5
09/22/2009 15:39:04,spid185,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=640 Available=640 Limit=640
09/22/2009 15:39:04,spid185,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=8700
09/22/2009 15:39:04,spid185,Unknown,Query Memory Objects: Grants=1 Waiting=0 Maximum=8060 Available=5690 Limit=8060
09/22/2009 15:39:04,spid185,Unknown,Global Memory Objects: Resource= 203 Locks= 10193 SE Schema Mgr= 286 SQLCache= 53 Replication= 2 ServerGlobal= 28 XPGlobal= 2 Xact= 5912 SETLS= 8 DatasetMemObjs= 16 SubpDescPmos= 8 SortTables= 2
09/22/2009 15:39:04,spid185,Unknown,Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
09/22/2009 15:39:04,spid185,Unknown,Procedure Cache: TotalProcs=37 TotalPages=790 InUsePages=327
09/22/2009 15:39:04,spid185,Unknown,Buffer Counts: Committed=524160 Target=524160 Hashed=347510 Internal Reservation=37068 External Reservation=2265 Stolen Potential=-2027 Min Free=384 Visible=185344 Available Paging File=27928731648
09/22/2009 15:39:04,spid185,Unknown,Buffer Distribution: Stolen=8584 Free=811 Cached=167255 Database (clean)=332621 Database (dirty)=14881 I/O=0
09/22/2009 15:39:04,spid185,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) VM Reserved = 4096 KB VM Committed = 4096 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 81520 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 496136 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 5104 KB MultiPage Allocator = 48 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 128 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 64 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 760 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 1496 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 272 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 2296 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 416 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 743272 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 32 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 8 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 88 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 8 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 56 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 40 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 144 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 1920 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 4200 KB MultiPage Allocator = 16 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 3288 KB MultiPage Allocator = 6024 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 72 KB MultiPage Allocator = 64 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 18960 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 480 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 6360 KB MultiPage Allocator = 16 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 2008 KB MultiPage Allocator = 192 KB
09/22/2009 15:39:04,spid185,Unknown,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
09/22/2009 15:39:04,spid185,Unknown,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 = 2080 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,MEMORYCLERK_SQLSTORENG (Total) VM Reserved = 9536 KB VM Committed = 9536 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 48816 KB MultiPage Allocator = 56 KB
09/22/2009 15:39:04,spid185,Unknown,MEMORYCLERK_SQLUTILITIES (Total) VM Reserved = 480 KB VM Committed = 480 KB AWE Allocated = 0 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 96 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 840 KB MultiPage Allocator = 88 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 728 KB MultiPage Allocator = 0 KB
09/22/2009 15:39:04,spid185,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) VM Reserved = 1620024 KB VM Committed = 48496 KB AWE Allocated = 4193280 KB SM Reserved = 0 KB SM Committed = 0 KB SinglePage Allocator = 0 KB MultiPage Allocator = 3624 KB
09/22/2009 15:39:04,spid185,Unknown,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 = 2944 KB MultiPage Allocator = 5432 KB
09/22/2009 15:39:04,spid185,Unknown,Memory node Id = 0 VM Reserved = 1651224 KB VM Committed = 78592 KB AWE Allocated = 4193280 KB SinglePage Allocator = 1405872 KB MultiPage Allocator = 15704 KB
09/22/2009 15:39:04,spid185,Unknown,Memory Manager VM Reserved = 1655384 KB VM Committed = 82600 KB AWE Allocated = 4193280 KB Reserved Memory = 1024 KB Reserved Memory In Use = 0 KB
09/22/2009 15:39:04,spid153,Unknown,There is insufficient system memory to run this query.
September 22, 2009 at 4:14 am
for 16 GB memory, I would suggest to enable the /PAE switch to enable SQL Server to see the available memory.
Any reason for limiting the SQL Server only to 4 GBwith AWE enabled?
This seems like memory overhead on SQL Server.
September 22, 2009 at 4:19 am
You have memory pressure issues, I would also wonder why you have limited sql server to only 4gb when you have 16gb of memory, in fact why enable AWE if you are limiting to 4GB. you should be allocating about 12GB to SQL Server, if it is a dedicated sql box.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 6:33 am
Thank you both of you for quick reply.
I forgot to mention we have total four instance on the server.
Two of them uses 4 GB each. One 3GB . Another one 1.5 GB (no awe).
Yes Vishal, PAE is enabled on this server
This is a cluster.
September 22, 2009 at 6:47 am
joeroshan (9/22/2009)
Thank you both of you for quick reply.I forgot to mention we have total four instance on the server.
Two of them uses 4 GB each. One 3GB . Another one 1.5 GB (no awe).
Yes Vishal, PAE is enabled on this server
This is a cluster.
That is not a lot of memory for 4 instances, I would think about upgrading to 32GB memory. Otherwise you would have to try and isolate the process that caused the memory pressure. I am assuming that the instances are patched to the latest service packs. You can try profiling it, sometime the cause of this error, is badly written t-sql.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 7:23 am
Thank you Silverfox.
We will plan for a memory upgrade. Or probably a 64 bit upgrade.
Meanwhile, if I setup a trace against the server, how will find out who is hurting memory. Should I look for high Reads/writes or CPU?
September 22, 2009 at 7:34 am
joeroshan (9/22/2009)
Thank you Silverfox.We will plan for a memory upgrade. Or probably a 64 bit upgrade.
Meanwhile, if I setup a trace against the server, how will find out who is hurting memory. Should I look for high Reads/writes or CPU?
You can do, and you can also use the performance dashboard, a lot of troubleshooting memory comes down to the DMV's. and looking at dbcc memorystatus for example.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply