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
  • 19,148
  • 4
  • 37
  • 58
Lucy
  • 299
  • 4
  • 7
  • 14

1 Answers1

6

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,376
  • 31
  • 42