Post reply

Lock Pages in Memory

  • October 21, 2010 at 8:42 am

    #157274

    Hi All,

    My Server Specs are as follows -

    OS - Microsoft Windows server 2008 R2 Enterprise -X64

    Ram -128 GB

    SQL Version : 9.00.4035.00 SP3 Enterprise Edition (64-bit)

    I am running into this following issue on the above server.

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

    Questions -

    1) Do I need to allow Lock Pages in memory for the SQl server account(Service Account) to aviod this error ? but BOL says it is not required for 64 based PC's

    2) Do I need to install Cunilative Update - 4 (Hot fix) on sql server 2005 to avoid above error ?

    Did anyone installed Update -4 Hot fix ?

    Any help is really appreciated ?

  • October 21, 2010 at 9:26 am

    #1240404

    sqlbee19 (10/21/2010)

    1) Do I need to allow Lock Pages in memory for the SQl server account(Service Account) to aviod this error ? but BOL says it is not required for 64 based PC's

    It's the easiest way to solve the issue.

    Another way is installing a (big) set of hotfixes that correct windows' memory trimming algorithm, that was quite aggressive in Win2003.

    To be honest, I have no idea if this applies to win2008 as well, but it's worth taking a look:

    http://support.microsoft.com/kb/918483/en-us/

    2) Do I need to install Cunilative Update - 4 (Hot fix) on sql server 2005 to avoid above error ?

    No. It's an OS issue, the cumulative update doesn't address OS issues.

    Did anyone installed Update -4 Hot fix ?

    Not yet.

    -- Gianluca Sartori

  • October 21, 2010 at 2:30 pm

    #1240593

    Thanks.

    Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

  • October 21, 2010 at 3:33 pm

    #1240628

    sqlbee19 (10/21/2010)

    Thanks.

    Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

    You should definitely set max memory so SQL doesn't starve out the OS. I would start with setting max memory somewhere in the 104-112GB range if the server with 128GB of memory is a dedicated SQL server. Less if it is not.

  • October 21, 2010 at 3:38 pm

    #1240631

    from the horses mouth............

    http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

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

  • October 21, 2010 at 3:42 pm

    #1240633

    did you look at the paging file...

    what is your paging file size and what is your physical server?

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • October 21, 2010 at 4:06 pm

    #1240646

    The paging size I allocated for windows is 100 GB as we have plenty of space.

    <<<<what is your physical server? >>>>

    I didnt get this question

  • October 21, 2010 at 4:22 pm

    #1240650

    sqlbee19 (10/21/2010)

    Thanks.

    Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

    The KB link posted above by Gianluca Sartori quotes the following

    Microsoft KB918483

    Windows Server 2008 improves the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. If you are running SQL Server on a Windows Server 2008-based computer, you do not have to manually configure the max server memory property as specified in the "Important considerations before you assign the 'Lock pages in memory' user right for an instance of a 64-bit edition of SQL Server " section.

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

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

  • October 22, 2010 at 3:13 am

    #1240776

    Did you check the article George suggested you?



    My blog

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

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