Solved

SQL 2005 32 Bits, Memory problem -

Posted on 2010-09-06
2
918 Views
Last Modified: 2013-11-21
Hello,

Today, I was creating Snap Shot for replication and I notice that I was received the following message very often on my server

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): 83348, committed (KB): 212836, memory utilization: 39%%.

I'm also getting this one :

here is insufficient system memory to run this query.

The server is running: Windows 2003 R2 Enterprise SP2 X86  and SQL 2005 STD SP2.
Memory option is set to use AWE, minimum memory allocated to the SQL server is 0 and Maximum is set to : 2147483647 MB

The server is having 20 GB physical memory installed

I also see a lot of errors regarding the memory in the SQL logs



09/06/2010 10:49:08,spid80,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:08,spid80,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
09/06/2010 10:49:08,spid80,Unknown,MEMORYBROKER_FOR_RESERVE <nl/> Allocations = 0 <nl/> Rate = 0 <nl/> Target Allocations = 337 <nl/> Future Allocations = 1103 <nl/> Last Notification = STABLE
09/06/2010 10:49:08,spid80,Unknown,MEMORYBROKER_FOR_STEAL <nl/> Allocations = 3520 <nl/> Rate = -118 <nl/> Target Allocations = 2522 <nl/> Future Allocations = 0 <nl/> Last Notification = SHRINK
09/06/2010 10:49:08,spid80,Unknown,MEMORYBROKER_FOR_CACHE <nl/> Allocations = 2878 <nl/> Rate = -100 <nl/> Target Allocations = 2059 <nl/> Future Allocations = 0 <nl/> Last Notification = SHRINK
09/06/2010 10:49:08,spid80,Unknown,Big Gateway <nl/> Configured Units = 1 Available Units = 1 Acquires = 0 <nl/> Waiters = 0 Threshold Factor = 8 Threshold = -1
09/06/2010 10:49:08,spid80,Unknown,Medium Gateway <nl/> Configured Units = 8 Available Units = 8 Acquires = 0 <nl/> Waiters = 0 Threshold Factor = 12 Threshold = 860842
09/06/2010 10:49:08,spid80,Unknown,Small Gateway <nl/> Configured Units = 32 Available Units = 30 Acquires = 2 <nl/> Waiters = 0 Threshold Factor = 250000 Threshold = 250000
09/06/2010 10:49:08,spid80,Unknown,Optimization Queue <nl/> Overall Memory = 44236800 Target Memory = 20660224 Last Notification = SHRINK Timeout = 6 Early Termination Factor = 5
09/06/2010 10:49:08,spid80,Unknown,Small Query Memory Objects: Grants=0 Waiting=0 Maximum=0 Available=0 Limit=0
09/06/2010 10:49:08,spid80,Unknown,Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=315
09/06/2010 10:49:08,spid80,Unknown,Query Memory Objects: Grants=0 Waiting=0 Maximum=11105 Available=11105 Limit=315
09/06/2010 10:49:08,spid80,Unknown,Global Memory Objects: <nl/> Resource= 871<nl/> Locks= 1172<nl/> SE Schema Mgr= 750<nl/> SQLCache= 68<nl/> Replication= 8<nl/> ServerGlobal= 27<nl/> XPGlobal= 2<nl/> Xact= 279<nl/> SETLS= 16<nl/> DatasetMemObjs= 32<nl/> SubpDescPmos= 16<nl/> SortTables= 2
09/06/2010 10:49:08,spid80,Unknown,Process physical/virtual memory pressure: 1/0 System physical memory pressure: 0
09/06/2010 10:49:08,spid80,Unknown,Procedure Cache: TotalProcs=25 TotalPages=553 InUsePages=257
09/06/2010 10:49:08,spid80,Unknown,Buffer Counts: Committed=6755 Target=6756 Hashed=384<nl/> Internal Reservation=1351 External Reservation=0<nl/> Stolen Potential=169<nl/> Min Free=2032 Visible=6756<nl/> Available Paging File=24301940736
09/06/2010 10:49:08,spid80,Unknown,Buffer Distribution: Stolen=3612 Free=124 Cached=2885<nl/> Database (clean)=70 Database (dirty)=21<nl/> I/O=0<c/> Latched=43
09/06/2010 10:49:08,spid80,Unknown,OBJECTSTORE_LOCK_MANAGER (Total) <nl/> VM Reserved = 4096 KB <nl/> VM Committed = 4096 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 9352 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,OBJECTSTORE_SERVICE_BROKER (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 256 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,OBJECTSTORE_SNI_PACKET (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 2072 KB<nl/> MultiPage Allocator = 48 KB
09/06/2010 10:49:08,spid80,Unknown,OBJECTSTORE_LBSS (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 336 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,USERSTORE_SXC (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 96 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,USERSTORE_OBJPERM (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 432 KB<nl/> MultiPage Allocator = 24 KB
09/06/2010 10:49:08,spid80,Unknown,USERSTORE_TOKENPERM (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 616 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,USERSTORE_DBMETADATA (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 264 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,USERSTORE_SCHEMAMGR (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 6008 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_SYSTEMROWSET (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 120 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_EVENTS (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 16 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERTO (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERREADONLY (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 32 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERRSB (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERUSERCERTLOOKUP (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERDSH (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERKEK (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_BROKERTBLACS (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 88 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_STACKFRAMES (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 0 KB<nl/> MultiPage Allocator = 8 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_XMLDBATTRIBUTE (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_XMLDBELEMENT (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_XMLDBTYPE (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_VIEWDEFINITIONS (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 16 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_NOTIF (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 16 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_TEMPTABLES (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 32 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_XPROC (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 24 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_PHDR (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 656 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_SQLCP (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 1504 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,CACHESTORE_OBJCP (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 1288 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 24 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SOSNODE (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 10016 KB<nl/> MultiPage Allocator = 7424 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_HOST (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 72 KB<nl/> MultiPage Allocator = 16 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_BHF (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 480 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLXP (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 16 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_FULLTEXT (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 16 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SNI (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 384 KB<nl/> MultiPage Allocator = 48 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLHTTP (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLSERVICEBROKER (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 64 KB<nl/> MultiPage Allocator = 192 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLCLR (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 8 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLCONNECTIONPOOL (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 1048 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLSTORENG (Total) <nl/> VM Reserved = 5760 KB <nl/> VM Committed = 5760 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 5496 KB<nl/> MultiPage Allocator = 280 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLUTILITIES (Total) <nl/> VM Reserved = 120 KB <nl/> VM Committed = 120 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 88 KB<nl/> MultiPage Allocator = 0 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLOPTIMIZER (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 3152 KB<nl/> MultiPage Allocator = 88 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLBUFFERPOOL (Total) <nl/> VM Reserved = 1573156 KB <nl/> VM Committed = 180516 KB <nl/> AWE Allocated = 54044 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 0 KB<nl/> MultiPage Allocator = 4552 KB
09/06/2010 10:49:08,spid80,Unknown,MEMORYCLERK_SQLGENERAL (Total) <nl/> VM Reserved = 0 KB <nl/> VM Committed = 0 KB <nl/> AWE Allocated = 0 KB <nl/> SM Reserved = 0 KB <nl/> SM Committed = 0 KB<nl/> SinglePage Allocator = 6192 KB<nl/> MultiPage Allocator = 4384 KB
09/06/2010 10:49:08,spid80,Unknown,Memory node Id = 0 <nl/> VM Reserved = 1602012 KB <nl/> VM Committed = 208404 KB <nl/> AWE Allocated = 54044 KB <nl/> SinglePage Allocator = 51408 KB<nl/> MultiPage Allocator = 17168 KB
09/06/2010 10:49:08,spid80,Unknown,Memory Manager <nl/> VM Reserved = 1606172 KB<nl/> VM Committed = 212412 KB <nl/> AWE Allocated = 54044 KB <nl/> Reserved Memory = 1024 KB <nl/> Reserved Memory In Use = 0 KB
09/06/2010 10:49:08,spid83,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:08,spid83,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
09/06/2010 10:49:07,spid93,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:07,spid93,Unknown,Error: 701<c/> Severity: 17<c/> State: 130.
09/06/2010 10:49:07,spid58,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:07,spid58,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
09/06/2010 10:49:07,spid105,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:07,spid105,Unknown,Error: 701<c/> Severity: 17<c/> State: 130.
09/06/2010 10:49:07,spid89,Unknown,There is insufficient system memory to run this query.
09/06/2010 10:49:07,spid89,Unknown,Error: 701<c/> Severity: 17<c/> State: 123.
09/06/2010 10:49:07,spid56,Unknown,Failed allocate pages: FAIL_PAGE_ALLOCATION 1
09/06/2010 09:35:10,spid1s,Unknown,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): 93584<c/> committed (KB): 212532<c/> memory utilization: 44%.
09/06/2010 09:33:03,spid1s,Unknown,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): 67844<c/> committed (KB): 212532<c/> memory utilization: 31%.


Any idea what I should do ?

Thanks a lot
0
Comment
Question by:brular01
2 Comments
 
LVL 29

Assisted Solution

by:Rich Weissler
Rich Weissler earned 100 total points
ID: 33614250
First time I've heard of this in the 32-bit version, but then I don't know anyone using large memory sets with Windows Enterprise either.  The good news is I've been watching this error on my system for some months now, but they haven't negatively impacted the system yet (yeah, that's not necessarily good news for you.)  The bad news is, I haven't seen a solution to the problem yet -- although I've heard of various hot fixes, and I'm eagerly awaiting SP4, which I'm hearing rumors about for mid-December.

Here are a few links as reading material:
http://support.microsoft.com/kb/918483
http://blogs.msdn.com/b/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
http://omaralzabir.com/a_significant_part_of_sql_server_process_memory_has_been_paged_out__this_may_result_in_performance_degradation/

The last one gives a link to some software I don't trust to attempt to run on my production system, and others report it hasn't solved it for them.  The problem also seems to affect SQL 2008.

Not a solution here - but figured it might jump start your research.
0
 
LVL 2

Accepted Solution

by:
ppdogs earned 400 total points
ID: 33614928
So you have 20GB physical RAM and you have allocated way above that to SQL (is this even corect 2147483647 MB)? Doesn't SQL service preallocate the RAM to itself.
try allocating like 18GB to SQL see if the problems goes away. i have a 10GB server with SQL assigned 8.5GB.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now