6

I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server 2017 like it was in SQL Server 2005, 2008 etc.?

9
  • Are you talking about the "Lock Pages In Memory" Security Policy? Oct 3, 2018 at 12:55
  • @RandiVertongen yes ,you could call it that
    – Lucy
    Oct 3, 2018 at 12:56
  • 1
    I have had to enable this on all my Windows 10 VMs running SQL Server locally, simply because of the hard trims - without LPIM, my plan cache was constantly getting wiped out. Server-class operating systems may be a different story these days, I would suggesting testing your full workload over a business cycle to see if you experience undue memory pressure in SQL Server, then try again with the setting enabled. I don't think you're going to get a simple "yes, you should always enable it" because it just isn't possible to know whether you should or not. Oct 3, 2018 at 14:23
  • 1
    This is older material obviously, but it's still essential reading today IMHO - even on more modern Windows Server versions I think those same hard trims are still possible. Oct 3, 2018 at 14:26
  • 1
    No matter on what environment you are running SQL Server, if you have carefully taken care of all other memory parameters of VM or SSD's or OS, you can go ahead with locked pages in memory. I have almost in all cases found it to be good
    – Shanky
    Oct 3, 2018 at 15:14

1 Answer 1

5

In general, I still recommend to enable the "Lock Pages in Memory" policy on the SQL Server service account. This instructs SQL Server to ignore Windows' requests to trim the working set and page it to disk.

As Erik points out in the comments, this is really needed when you need to preserve SQL Servers' working set, which is necessary when the same Windows machine hosts other services or applications that may end up competing with SQL Server for RAM.

Another thing to keep in mind is Virtualization: nowadays almost all SQL Servers are installed on virtual machines, with several possible memory configurations and often sharing the same hypervisor host with many other VMs. Overcommitting memory is commonplace and the hypervisor tries to balance RAM allocations to VMs using memory balooning. Even if your SQL Server is not sharing the same Windows guest with other services, you might want to enable LPIM to let it play nicely with memory balooning and avoid unwanted memory trimming on production workloads.

Here is a whitepaper from Microsoft describing how to configure Hyper-V and SQL Server.

That said, if this is a test instance, you probably don't care much about the performance implications of memory trimming.

4
  • You said "This instructs SQL Server to ignore Windows' requests to trim the working set and page it to disk." I would like to make slight correction. Assuming max and min server memory are not same, even if there is LPIM, in case of OS flagging low memory notification SQL Server will still try to trim its working set till min server memory and after that if there is still severe OS memory pressure, OS will be paged.
    – Shanky
    Oct 3, 2018 at 15:27
  • 2
    Locked pages are never paged to disk. They bypass the Windows Virtual Memory Manager entirely, mapping a virtual address range directly to RAM. So SQL Server will only release memory co-operatively in response to memory pressure. Windows cannot simply remove locked pages from RAM like it can with Virtual Memory allocations. Oct 3, 2018 at 15:44
  • @David Browne There is difference between paging and working set trimming. What I said and AFAIK even if there is LPIM and min server memory is set lower to max server memory, in event of memory pressure SQL Server will try to trim down to min server memory. Trimming and paging are 2 different things
    – Shanky
    Oct 4, 2018 at 6:28
  • I've heard from a colleague that Microsoft discourages using Lock pages in memory on Azure hosted VMs that run SQL, as apparently it can reduce overall performance. I haven't been able to find a link however -- so if anyone finds such a thing, please share it here.
    – Mike
    May 5, 2020 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.