Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"A significant part of sql server process memory has been paged out. This may result in a performance degradation" Expand / Collapse
Author
Message
Posted Monday, January 18, 2010 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 7, 2010 7:02 AM
Points: 7, Visits: 56
Hello, I have the following problem:

"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): 92984, committed (KB): 1688060, memory utilization: 5%."

I get this error every two-three minutes aproximatly, since 10 days ago. My server has the following caracteristics:

Windows server 2003 Standar Edition SP2 (32bits)
intel(R) Xeon(TM) CPU 3.6GHz and 3.5GB de RAM.

SQL Server 2005 Standar Edition 9.00.3068.00 SP2 (32bits).

SQL Server hasn´t allocated MIN nor MAX memory.

I enclose two files with performance and administration task.

I check the counter 'SQL Server Buffer: Buffer Cache Hit Ratio' for half an hour and the average value
is 99% aproximatly.

Now, my sql server doesn´t response, it´s very very very slow.

How can I solve this problem??, which causes are??. It`s emergency. I need help.

Thanks!!.


  Post Attachments 
performance.png (59 views, 17.28 KB)
Process.png (49 views, 16.34 KB)
Post #849024
Posted Tuesday, January 19, 2010 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
Sounds like hard trimming.
This covers this issue and provides a link to further information
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

It can also - from what I've seen - happen if there's a problem with your hardware, check this out as well, particularly if this is a recent occurence on a box which has been running fine until now. In this case, you may be looking at moving to other servers - fixing the hardware and moving back
Post #849807
Posted Tuesday, January 19, 2010 5:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2014 1:44 PM
Points: 1, Visits: 48
Hi,
add in windows 2003 server boot.ini, /3GB, restart server.
execute sp_configure 'max server memory', '2700 MB'
RECONFIGURE
increase your tempdb files. 4 cpus, 4 tempdb files with 1024 gb size. see free disk space.
see windows pagefile.sys size, +- 3096 gb. put your tempdb files in separate disk exclusive if is possible.
processes sql monitoring with, sp_who, open_tran>0, blockby >0 in sys.sysprocesses. processes with diskio big values. ajust you aplication, build indexes, etc.
Good job.
Bye.
Post #850166
Posted Tuesday, January 19, 2010 9:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 36,978, Visits: 31,500
muruecos (1/18/2010)
Hello, I have the following problem:

"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): 92984, committed (KB): 1688060, memory utilization: 5%."

I get this error every two-three minutes aproximatly, since 10 days ago. My server has the following caracteristics:

Windows server 2003 Standar Edition SP2 (32bits)
intel(R) Xeon(TM) CPU 3.6GHz and 3.5GB de RAM.

SQL Server 2005 Standar Edition 9.00.3068.00 SP2 (32bits).

SQL Server hasn´t allocated MIN nor MAX memory.

I enclose two files with performance and administration task.

I check the counter 'SQL Server Buffer: Buffer Cache Hit Ratio' for half an hour and the average value
is 99% aproximatly.

Now, my sql server doesn´t response, it´s very very very slow.

How can I solve this problem??, which causes are??. It`s emergency. I need help.

Thanks!!.


What changed 10 days ago? Answer that and you'll have the solution to your problem. Do you have a "runaway" query running? Did someone install some new software or hardware on the server? Did someone promote some code or a change to the code?

If it hasn't been overwritten yet, check the default trace file... IIRC, it's in the same directory as the SQL Server log files which you might also want to check. Also, check the Windows Server error logs...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #850217
Posted Wednesday, February 3, 2010 8:13 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:32 AM
Points: 372, Visits: 414
I suspect you are using some process which is using nay extended procedures or clr ones causing memory leak

or

Have a external backup utility like veritas or litespeed purging memory

or

in any case set the max and min memory (remember both) to same value so that trimming is restricted completely for windows, and also enable lock pages in memory option in case its not already done



Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Post #859257
Posted Thursday, February 4, 2010 5:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 4,348, Visits: 6,157
If an "emergency" had been crushing my server for TEN DAYS I would long since have been on the phone to Microsoft Support or brought in a performance tuning consultant to fix my problem. Shouldn't take more than a few hours of work to identify/correct the problem.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #859553
Posted Tuesday, April 19, 2011 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 4:32 AM
Points: 2, Visits: 251
It is looking like memory page out. Have you changed sql server service account recently? and not changed the same for "Lock pages in memory"? Have a look at it.

If the resource utilization for the node is high and it can not even recieve the heart beat, there are chances for a failover.

Regards
Mahesh
Post #1096072
Posted Tuesday, April 19, 2011 11:47 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 42,817, Visits: 35,939
Please note: year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1096081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse