Post reply

'A significant part of sql server process memory has been paged out...' error

  • December 5, 2008 at 5:11 pm

    #154992

    Hello!

    This is a follow up to my previous post:

    '

    We have just configured SQL Server 2005 64-bit two node active/passive cluster with 32 GB of RAM. I have limited SQL Server memory usage to 26 GB. Total Server Memory (KB) shows value 16MB at the moment. Target Server Memory (KB) is showing 27262976 (26 GB).

    ...'.

    I have noticed that there are multiple messages (not just on start up) in SQL Server error log:

    'Message

    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 331 seconds. Working set (KB): 95276, committed (KB): 204776, memory utilization: 46%.

    '.

    We are running SQL Server 2005 SP2 64-bit Standard Edition on Windows 2003 Enterprise R2 64-bit and 'Lock Pages in Memory' is ignored.

    I did some research and the general consensus is if this error only shows up on start up it is OK. But it is not the case in my enviroment. I am really concerned about performance implications and hesitant to start using this enviroment.

    Any advice is greately appreciated.

    Thanks,

    Igor

  • December 6, 2008 at 4:14 am

    #908789

    begs the question, what other apps are running on these sql servers?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 6, 2008 at 10:23 am

    #908827

    This is a brand new server we just configured. There is no other applications besides SQL Server and OS. SQL Server doesn't have any user databases and nobody and no users connected to the server.

  • December 7, 2008 at 12:45 pm

    #908962

    This can be a common error in SQL 2005 x64 standard, as per http://support.microsoft.com/kb/918483

    There are links to a couple of hotfixes as regards terminal services potentially causing this issue, but an upgrade to Enterprise Edition might be in the works.


    Shamless self promotion - read my blog http://sirsql.net

  • December 7, 2008 at 1:31 pm

    #908969

    Thanks Nicholas! I saw several cases like mine and people were reporting that Microsoft came up with hotfix for their enviroment. I was wondering if this fix might have been included in on the patches after SP2. Unfortunately, upgrading to EE is out of question because of the money difference.

  • December 7, 2008 at 1:44 pm

    #908971

    Have you tried going from direct memory allocation to just setting a hard value? Maybe that would help with paging (but then again maybe not).


    Shamless self promotion - read my blog http://sirsql.net

  • December 7, 2008 at 2:29 pm

    #908972

    I have configured max value used by SQL Server leaving 6GB to OS. The min value is currently set to 0.

    Is that what you meant?

  • December 7, 2008 at 2:36 pm

    #908973

    configure the min value to be a sensible value other than zero but not higher than max

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 7, 2008 at 2:37 pm

    #908974

    Try setting them the same. It should just grab that memory right away, not saying it'll do anything, but it might.


    Shamless self promotion - read my blog http://sirsql.net

  • December 7, 2008 at 6:13 pm

    #908982

    I will try setting min memory to the same value as max and report back the progress.

Viewing 10 posts - 1 through 9 (of 9 total)

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