Lock pages in memory setting

  • vsamantha35

    SSChampion

    Points: 10983

    Hi Experts,

    I have a doubt on lock pages in memory.

    We have sql 2012/2016 64-bit enviroments.

    Question is, do we need explicity enable lock pages for 64bit sql servers or it is not required?

    As per my knowledge it is used for 32-bit systems as virtual address space is low.

    Thanks,

    Sam

  • Andy sql

    SSCrazy Eights

    Points: 9368

    This has been a long running debate.... for example, see:

    https://www.red-gate.com/simple-talk/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

    Personally, I don't see a danger in enabling it on x64 systems (and ensuring the MSSQL service account is granted the correct permissions). It will help ensure your MSSQL instance has the memory you want it to have. On the other hand, it is not *required* so it remains as your informed choice.

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88074

    Just be aware that if you enable it - you *must* set a max memory for SQL Server.  If you do not set the max memory then SQL Server will eventually consume all memory available and starve the OS (and other processes).

    If you do not set locked pages - SQL Server can still take all memory - but the OS can request that memory back and it will be granted (eventually).  With locked pages - SQL Server will not release that memory back to the OS.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 3 posts - 1 through 3 (of 3 total)

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