Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"A significant part of sql server process memory has been paged out. This may result in a...


"A significant part of sql server process memory has been paged out. This may result in a performance degradation"

Author
Message
muruecos
muruecos
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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!!.
Attachments
performance.png (61 views, 17.00 KB)
Process.png (50 views, 16.00 KB)
Andrew Gothard-467944
Andrew Gothard-467944
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 2357
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
marcelomiranda
marcelomiranda
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 52
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45329 Visits: 39936
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Prakash Heda
Prakash Heda
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 416
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6004 Visits: 8314
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
Cheveri
Cheveri
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 268
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47388 Visits: 44399
Please note: year old thread.


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

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search