September 6, 2010 at 2:04 pm
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 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
Any idea what I should do ?
Thanks a lot
September 6, 2010 at 3:58 pm
If you are running SQL Server 2005 32-bit, even with AWE you are limited to 8GB per instance, and everything above 4GB is limited to the buffer pool, so things such as reserved memory used for CLR-based procedures does not have access to the extra memory.
Here are a few things I would look at:
- Ensure you specify min/max memory settings in SQL
- If you have AWE set, ensure your boot.ini has the correct startup parameters (ex: /PAE)
- Check to see if there is memory contention with other services/applications running on the same machine
- Check SQL Agent jobs, scheduled tasks, backup schedules to try and identify if what besides normal user activity is going on during the timeframes you are receiving errors for memory pressure
- Check SQL logs
- Check Windows system/application logs
If you have 20GBs of memory installed, there is no way you are going to be able to directly access it unless you are running SQL 64-bit.
Joie Andrew
"Since 1982"
September 6, 2010 at 5:03 pm
Hi,
thanks for the help and your reply.
Server is running X86
- As i said : min is set to 0 and maximum is to 2147483647 ( 2 TB )
- Boot.ini contain the /PAE
[boot loader]
timeout=3
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
- This machine is decidated to SQL i d'ont have anything else running
- When I received the message, nobody was using the system, nothing was running except the snapshot creation
- I got few message in my SQL errors logs, see below :
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%.
I d'ont see anything special in the Apps or System log of the server.
thanks a lot !
September 6, 2010 at 5:33 pm
You should still set the min/max values for memory in SQL to ensure a couple of things:
- That SQL will not be robbed of a minimum amount of memory to run by other operations on the machine
- That SQL will not take too much memory if other operations need it. Even though the memory manager in the database engine will release memory if requested by the OS, that process incurs overhead, and can cause operations to timeout
AWE address is only fully addresable by the buffer pool. You may be getting memory pressure from things such operations that need to use reserve memory. Try restarting SQL using the -g parameter and set SQL to use something like 1GB as a starting point, retry your operation and see if you get the same error.
Joie Andrew
"Since 1982"
September 6, 2010 at 5:48 pm
Hi,
What's should I put as Min & MAX ? It's a dedicated SQL Server so it was thinking min 10 GB / max 16 GB ?
September 6, 2010 at 6:19 pm
That seems fine for a starting point. Depending on your specific needs and work loads the settings may need to be tweaked in the future, but that looks okay to me.
You probably should still look at restarting the server with the -g parameter setting 1GB though.
Joie Andrew
"Since 1982"
September 6, 2010 at 7:05 pm
Because you are running Standard Edition on SP2 - you do not have the advantage of using locked pages in memory.
You'll need to set even lower max memory than 16GB - I would recommend 12GB to start and see if you still have issues. If not, you can then increase by 1GB and verify you still don't have any issues.
Or, you can upgrade to SP3 CU4 (or higher) and use locked pages in memory policy. That might allow you to use 16GB of memory without problems, but I wouldn't try to set it any higher than that.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2010 at 7:17 pm
Thanks for all the reply, i will try it and let you know.
I'm running 2003 R2 SP2 Enterprise and SQL 2005 STD, all in X86
I might also try the solutions below :
1- Enable the Lock Pages in Memory Option (Windows)
http://msdn.microsoft.com/en-us/ms190730.aspx
2- Install the followin MS Hotfix
http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739
you may experience the following symptoms:
Working set of certain applications, such as SQL Server, may be trimmed or paged out
3- Run memory Diagnostic tools
September 6, 2010 at 7:22 pm
Again, you will need to upgrade to at least SP3 CU4 or the locked pages in memory option won't be honored. You also have to start SQL Server with a specific flag - I believe.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply