Post reply

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

  • January 18, 2010 at 2:30 am

    #219386

    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!!.

  • January 19, 2010 at 8:42 am

    #1105475

    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

  • January 19, 2010 at 5:38 pm

    #1105782

    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.

  • January 19, 2010 at 10:00 pm

    #1105823

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 3, 2010 at 8:13 pm

    #1113399

    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

  • February 4, 2010 at 5:47 am

    #1113657

    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 on googles mail service

  • April 19, 2011 at 11:26 pm

    #1313727

    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

  • April 19, 2011 at 11:47 pm

    #1313733

    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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply