none
A significant part of sql server process memory has been paged out

    Question

  • I am struggling to get SQL 2008 Std 64-bit running in an acceptable manner on Server 2008 Enterprise prior to loading my user databases.  I have 16 GB of RAM installed, but as soon as I start the SQL Server service I get the following message written to the SQL Server logs.

     

    “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): 54120, committed (KB):  113336, memory utilization: 47%.”

    I have been through a ton of articles talking about locking pages into memory, applying hotfixes, disabling TCP chimmney stack enhancements, setting LargeSystemCache registry values, and updating Broadcom NIC drivers. 

    None of it has helped, and given that this is a brand new installation with no users, I really do not see why these "hacks" are needed.  Am I missing something?

    Tuesday, December 30, 2008 11:06 PM

All replies

  • Default settings, minimum 0, maximum unlimited.

    I tried setting the minimum to 5000 KB, but still got there error on SQL Server startup.

    Wednesday, December 31, 2008 2:28 PM
  • Unfortunately, "Lock Pages in Memory" only works on Enterprise Edition.  You should change the Max Server Memory setting to something like 12000-13000 (depending on what SQL Server features you are using and what else may be running on the machine).  Max Server Memory only controls how much memory the Buffer pool can use. You need to make sure the OS is not under memory pressure.

    If there is any way for you to get SQL Server 2008 Enterprise Edition (expensive, I know), you will be MUCH happier, since you can use Lock Pages In Memory, Online Index operations, data and backup compression, etc.
    Wednesday, December 31, 2008 4:55 PM
  • No chance of purchasing SQL Enterprise.  I had a hard enough time getting Windows Server Enterprise to support this 2-node SQL cluster.

    I will set a maximum just to establish a ceiling, but if stuff is already being paged out?.... Not sure how this is going to solve anything.

    The OS is not under memory pressure, there is nothing installed on this server except SQL, and no file shares are active.
    Monday, January 05, 2009 2:50 PM
  • Here is what Microsoft'd Bob Dorr has to say about this (it was on Windows 2003 and SQL 2005):

    http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
    Monday, January 05, 2009 5:44 PM
  • And the circle is complete.
    That is actually where I started on my quest to resolve this.  I will pursue a resolution with Microsoft tech support.
    Wednesday, January 07, 2009 5:24 PM
  • Hi,

     Can someone please explain where this "Lock Pages in Memory rights not available in Standard Edition" comes from? I don't have a 2008 in front of me but..

    AWE (dynamic) is available in the Standard Edition (http://msdn.microsoft.com/en-us/library/cc645993.aspx for full feature list) and in order to use the AWE APIs, Lock Pages in Memory is necessary. Maximum memory in Standard Edition is only limited by OS (http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx), 32 or 64 bit.

    Secondly, Lock Pages in Memory is a user right in the OS so I don't see how whatever edition you have of SQL Server would affect anything in the first place -  although in Workgroup and lower editions, AWE is not available so then it doesn't make much sense to grant Lock Pages in Memory.

    /Elisabeth

    Elisabeth Rédei | www.sqlserverland.com | MCITP, MCT | http://www.linkedin.com/elisabethredei
    Wednesday, January 07, 2009 7:46 PM
  • Elizabeth,

    It is pretty well documented (at least in multiple blog posts by MVPs, Microsoft CSS, SQL Dev Team members, etc. ), that Lock Pages In Memory only works with Enterprise Edition. I agree that it does not make sense. It is really that way to convince people to buy Enterprise Edition instead of Standard Edition (IMHO).  Here are a couple of links:

    http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

    http://support.microsoft.com/kb/918483

     

    Wednesday, January 07, 2009 7:57 PM
  • Thanks Glenn!

    I did see some non-MS blogs about this, but the two links you sent says nothing about AWE not being available in 2008 Standard Edition (presumably 64-bit). Although it does say it is not available for 64-bit 2005 standard edition which sounds really strange but admittedly I have have not worked with many Standard Edition servers (it's sounds more like a bug than a locked feature - I'd like to listen to a salesperson who tries to sell in 64-bit Enterprise Edition based on the ability to "lock pages in memory" :D ). 

    /Elisabeth


    Elisabeth Rédei | www.sqlserverland.com | MCITP, MCT | http://www.linkedin.com/elisabethredei
    Wednesday, January 07, 2009 8:13 PM
  • I want to clarify something here. The lock pages in memory right can be used by both SQL Server 2005 Standard and Enterprise 32-bit. The lock pages in memory right can only be used by the Enterprise version when you're using 64-bit. Please refer to the following article for more information: http://support.microsoft.com/kb/918483

    I hope this helps.

    Justin
    Thursday, January 08, 2009 5:57 AM
  • Hi Justin,

    Yes I think we concluded that for 2005 only Enterprise Edition can use AWE (however strange the concept is) but for 2008 doc's actually say "AWE is not needed and cannot be configured on 64-bit operating systems."

    /Elisabeth
    Elisabeth Rédei | www.sqlserverland.com | MCITP, MCT | http://www.linkedin.com/elisabethredei
    Thursday, January 08, 2009 6:17 AM
  • Hello ,

    Looking at your Working set and committed memory(Working set (KB): 54120, committed (KB):  113336). This is nothing but a false warning and you can ignore it.

    Read the below blog  http://blogs.msdn.com/b/psssql/archive/2009/05/12/sql-server-reports-working-set-trim-warning-message-during-early-startup-phase.aspx

    Note: You can ignore the above warning only if the value in committed is ver low. If the value in committed is high follow the steps in

    http://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/ to find the root cause of working set trim and to clarify your doubts on lock pages in memory.

     

    Thank you,

    Karthick P.K |My Facebook Page |My Site| Blog Space |Twitter

    www.Mssqlwiki.com

    Please click the Mark as answer button if this reply solves your problem.

    Friday, September 21, 2012 7:16 AM