Solved

SQL 2005 32 Bits, Memory problem -

Posted on 2010-09-06
2
923 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 30

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

770 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