June 29, 2009 at 9:47 am
Hi, I am receiving the errors listed below, all the answers I have found so far seem to be tied to 32 bit systems using a switch in the boot.ini file. I don't believe those are required in a 64 bit system. I have a multi-instance cluster running on Windows 64-bit 2003 with 64-bit Enterprise edition of sql 2005 SP3. the cluster has 65GB of memory, so I don't think it should be paging very much. Over 600Gb of free disk space on the SAN. Any help would be appreciated, let me know if you need more info. Thanks in advance! Chris
2009-06-29 09:06:07.35 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 135300, committed (KB): 65823168, memory utilization: 0%.
2009-06-29 09:09:50.44 spid74 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT 8192
2009-06-29 09:09:51.18 spid74
Memory Manager
VM Reserved = 134648184 KB
VM Committed = 67736432 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
2009-06-29 09:09:51.20 spid74
Memory node Id = 0
VM Reserved = 134642424 KB
VM Committed = 67730760 KB
AWE Allocated = 0 KB
SinglePage Allocator = 358760 KB
MultiPage Allocator = 26472 KB
2009-06-29 09:09:51.20 spid74
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 = 4856 KB
MultiPage Allocator = 8176 KB
2009-06-29 09:09:51.20 spid74
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 134316032 KB
VM Committed = 67405184 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 520 KB
2009-06-29 09:09:51.20 spid74
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 = 244192 KB
MultiPage Allocator = 24 KB
2009-06-29 09:09:51.20 spid74
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 = 1136 KB
MultiPage Allocator = 128 KB
2009-06-29 09:09:51.20 spid74
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 = 58000 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.20 spid74
MEMORYCLERK_SQLSTORENG (Total)
VM Reserved = 35840 KB
VM Committed = 35840 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8256 KB
MultiPage Allocator = 728 KB
2009-06-29 09:09:51.20 spid74
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 = 736 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.20 spid74
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
2009-06-29 09:09:51.20 spid74
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 = 96 KB
MultiPage Allocator = 304 KB
2009-06-29 09:09:51.20 spid74
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
2009-06-29 09:09:51.20 spid74
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 = 64 KB
MultiPage Allocator = 16 KB
2009-06-29 09:09:51.20 spid74
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
2009-06-29 09:09:51.20 spid74
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
2009-06-29 09:09:51.20 spid74
MEMORYCLERK_QSRANGEPREFETCH (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
2009-06-29 09:09:51.20 spid74
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 = 32 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.20 spid74
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 = 7578432 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.20 spid74
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 = 416 KB
MultiPage Allocator = 448 KB
2009-06-29 09:09:51.20 spid74
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 = 10520 KB
MultiPage Allocator = 15776 KB
2009-06-29 09:09:51.20 spid74
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
2009-06-29 09:09:51.21 spid74
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 = 1176 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 3840 KB
MultiPage Allocator = 16 KB
2009-06-29 09:09:51.21 spid74
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 = 1008 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 16 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 24 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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 = 104 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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
2009-06-29 09:09:51.21 spid74
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 = 728 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 6488 KB
MultiPage Allocator = 16 KB
2009-06-29 09:09:51.21 spid74
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 = 3784 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 608 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 3440 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 16 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 80 KB
2009-06-29 09:09:51.21 spid74
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 = 840 KB
MultiPage Allocator = 48 KB
2009-06-29 09:09:51.21 spid74
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 = 272 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74
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 = 7536 KB
MultiPage Allocator = 0 KB
2009-06-29 09:09:51.21 spid74 Buffer Distribution: Stolen=62263 Free=956 Cached=3784
Database (clean)=8205126 Database (dirty)=12921
I/O=162, Latched=24
2009-06-29 09:09:51.21 spid74 Buffer Counts: Committed=8285232 Target=8285851 Hashed=8218406
Internal Reservation=1657170 External Reservation=926477
Stolen Potential=6878822
Min Free=1584 Visible=8285851
Available Paging File=4927488
2009-06-29 09:09:51.21 spid74 Procedure Cache: TotalProcs=17 TotalPages=757 InUsePages=513
2009-06-29 09:09:51.21 spid74 Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
2009-06-29 09:09:51.35 spid74 Global Memory Objects:
Resource=577
Locks=945
SE Schema Mgr=811
SQLCache=230
Replication=2
ServerGlobal=60
XPGlobal=2
Xact=442
SETLS=19
DatasetMemObjs=32
SubpDescPmos=79
SortTables=2
2009-06-29 09:09:51.35 spid74 Query Memory Objects: Grants=3 Waiting=0 Maximum=6213715 Available=5266411 Limit=6213715
2009-06-29 09:09:51.35 spid74 Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=6226515
2009-06-29 09:09:51.35 spid74 Small Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800
2009-06-29 09:09:51.36 spid74
Optimization Queue
Overall Memory = 54408167424 Target Memory = 51727032320 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
2009-06-29 09:09:51.36 spid74
Small Gateway
Configured Units = 64 Available Units = 64 Acquires = 0
Waiters = 0 Threshold Factor = 380000 Threshold = 380000
2009-06-29 09:09:51.36 spid74
Medium Gateway
Configured Units = 16 Available Units = 16 Acquires = 0
Waiters = 0 Threshold Factor = 12 Threshold = -1
2009-06-29 09:09:51.36 spid74
Big Gateway
Configured Units = 1 Available Units = 1 Acquires = 0
Waiters = 0 Threshold Factor = 8 Threshold = -1
2009-06-29 09:09:51.36 spid74
MEMORYBROKER_FOR_CACHE
Allocations = 3789
Rate = 6
Target Allocations = 6277074
Future Allocations = 0
Last Notification = GROW
2009-06-29 09:09:51.36 spid74
MEMORYBROKER_FOR_STEAL
Allocations = 41058
Rate = -2
Target Allocations = 6314335
Future Allocations = 0
Last Notification = GROW
2009-06-29 09:09:51.36 spid74
MEMORYBROKER_FOR_RESERVE
Allocations = 947304
Rate = 0
Target Allocations = 6641622
Future Allocations = 1553428
Last Notification = GROW
2009-06-29 09:11:36.17 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 330 seconds. Working set (KB): 22629984, committed (KB): 65749272, memory utilization: 34%.
2009-06-29 09:15:49.40 spid2s Warning: The available page file space has dropped below 4Mb.
2009-06-29 09:17:35.08 spid70 Error: 701, Severity: 17, State: 123.
2009-06-29 09:17:35.08 spid70 There is insufficient system memory to run this query.
2009-06-29 09:17:35.31 spid71 Error: 701, Severity: 17, State: 123.
2009-06-29 09:17:35.31 spid71 There is insufficient system memory to run this query.
2009-06-29 09:19:56.61 spid70 Error: 701, Severity: 17, State: 123.
2009-06-29 09:19:56.61 spid70 There is insufficient system memory to run this query.
2009-06-29 09:20:00.06 spid70 Error: 701, Severity: 17, State: 123.
2009-06-29 09:20:00.06 spid70 There is insufficient system memory to run this query.
June 29, 2009 at 9:54 am
Have you set the max memory for the SQL instances? If not, each instance will try to use all available memory, starving your OS.
June 29, 2009 at 10:00 am
I haven't but there are only 2 instances of sql and they are each running on opposite sides of the cluster so I don't think they would have a negative affect on the memory of each server.
Thanks,
Chris
June 29, 2009 at 10:04 am
On 64-bit you need to set max memory. Otherwise SQL will allocate until the OS is memory starged, and then it will get paged out (if the SQL service account doesn't have the lock pages privilege) or will cause OSm instability and even a crash ( if the SQL service account does have the lock pages privilege)
With 64GB memory, set SQL's max memory to no more than 56GB, then check that the SQL service account has permission to lock pages. You'll find that in the local security policy.
Edit: The recommendation of 56GB is assuming there's nothing other than SQL on the box. If there is, drop that value down to allow whatever else is there to run.
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
June 29, 2009 at 10:20 am
I set the memory to a hard amount and had our network team grant the locking pages to the sql account. I will follow-up with the results
Thanks Again!!
Chris
July 1, 2009 at 7:31 am
that resolved the issue, thanks again for your assistance:-D
November 29, 2010 at 8:10 am
We have 4 GB memory on our server and it is 64 bit Windows 2008 Server SR2, SQL Server 2008 SR2. Max memory has some strange number in it, 2147483647. Should I set it to 3 GB - 3,000 MB?
November 29, 2010 at 8:12 am
Please post new questions in a new thread. Thank you.
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
November 29, 2010 at 8:19 am
sorry, go ahead a delete this.. i posted in the 2008 config
January 17, 2011 at 9:16 am
Gila,
I am interested to know how you came up with the 56GB that you state above.
Thanks,
Fraggle
August 6, 2013 at 5:33 am
We've just had the same SQL server error - ( error 701, severity 17, state 123 ). Horrible to track down as even when the server was reporting problems, no SQLs were running! Turned out to be a memory leak in a stored procedure that handled XML (not closing the document properly).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply