none
SQL Server 2016 Enterprise with SP1 - To use Lock pages or not to use Lock pages

    Question

  • Hi folks,

    I installing two SQL Server 2016 Enterprise with SP1 Instaces on one Windows Server 2012 R2 Standard Server, so it is a active/active cluster on a physical server. I thinking about to use Lock pages for my sql instances, but I found only outdated Information about this issue. Are there any actual Information/Recommondations about Lock Pages (hopefully from ms).

    Peter

    Tuesday, March 14, 2017 8:18 AM

All replies

  • We are about to upgrade our server in the few months and I definitely try to use this option especially in regard there is a plan cache flush happens rapidly on SQL Server 2016. Sure , it also depends how much memory does the server have and what MAX memory setting you have assigned to SQL Server .....    

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, March 14, 2017 8:29 AM
  • Lock pages are always helpful on physical Machine. I suggest you leave enough memory for OS and fix appropriate max server memory and then go ahead and grant SQL Server service account LPIM. For your scenario if enough memory is left for OS and sufficient memory is given to SQL Server LPIM has no drawback

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, March 14, 2017 9:07 AM
    Moderator
  • Each Server has 192 GB RAM, I think to give

    - SQL Server 1: Min Memory = 50GB and max 80GB

    - SQL Server 2: Min = 10GB and max 40GB

    Tuesday, March 14, 2017 2:35 PM
  • -> I think Lock pages in memory can be set for SQL server on Physical server if the server is dedicated database server with no other application using much memory. It would be wise to monitor AvailableMB, PLE, Lazywrites, stalls and come up with a good max server memory. Then setting LPIM should not be an issue. I think after enabling LPIM, I dont think we may need min. server memory.

    -> There could be issues with LPIM on virtual servers. It is documented in https://blogs.vmware.com/apps/2012/12/when-to-use-lock-pages-in-memory-with-sql-server.html .

    Tuesday, March 14, 2017 3:07 PM
  • Each Server has 192 GB RAM, I think to give

    - SQL Server 1: Min Memory = 50GB and max 80GB

    - SQL Server 2: Min = 10GB and max 40GB

    I would not go for setting min server memory unless you really need it. To set max server memory see this thread

    and with multiple instances LPIM becomes even more necessary


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, March 14, 2017 4:15 PM
    Moderator