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.?

Shanky
  • 18,985
  • 4
  • 35
  • 58
Lucy
  • 299
  • 4
  • 7
  • 14
  • Are you talking about the "Lock Pages In Memory" Security Policy? – Randi Vertongen Oct 03 '18 at 12:55
  • @RandiVertongen yes ,you could call it that – Lucy Oct 03 '18 at 12:56
  • It's only recommended under specific circumstances, where other processes (including other SQL Server installs, and secondary components like SSIS etc.) contend with SQL Server for memory -- do you have those? – Erik Darling Oct 03 '18 at 13:02
  • @sp_BlitzErik I'm going to be running just SSIS and SSRS reports ,those are the two services that this Server will be assigned to do (This will be a Testing server ) – Lucy Oct 03 '18 at 13:12
  • 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. – Aaron Bertrand Oct 03 '18 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. – Aaron Bertrand Oct 03 '18 at 14:26
  • @AaronBertrand interesting, I haven't had those issues with my Win 10 VMs. Have you tried monitoring them to see if something else is clearing the cache? ;) – Erik Darling Oct 03 '18 at 14:32
  • 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 03 '18 at 15:14
  • @sp_BlitzErik Haha, I didn't have to go that far, LPIM was the first thing I tried and it solved the issue. I will do some tests though on a new VM where I haven't yet applied anything. – Aaron Bertrand Oct 03 '18 at 15:49

1 Answers1

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.

spaghettidba
  • 11,266
  • 30
  • 42
  • 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 03 '18 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. – David Browne - Microsoft Oct 03 '18 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 04 '18 at 06: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 05 '20 at 09:53