Lock pages in memory for system accounts

Question

Yes, well where to start? If you really enjoy installing and fine-tuning Microsoft SQL Server or Oracle RDBMS instances on Microsoft Windows Server, then at some point you will all come across the recommendation to "....lock pages in memory.".

Lock Pages in Memory

Here are the links to the relevant documents for your convenience and the steps described for Oracle RDBMS and Microsot SQL Server.

Microsoft SQL Server 2017

• Enable the Lock Pages in Memory Option (Windows) (Microsoft | SQL Docs)

1. On the Start menu, click Run. In the Open box, type gpedit.msc.

2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.

3. Expand Security Settings, and then expand Local Policies.

4. Select the User Rights Assignment folder.

   The policies will be displayed in the details pane.

5. In the pane, double-click Lock pages in memory.

6. In the Local Security Setting - Lock pages in memory dialog box, click Add User or Group.

7. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.

8. Restart the SQL Server Service for this setting to take effect.

Oracle RDBMS 18c

• 8 Tuning Windows to Optimize Oracle Database (Oracle | Docs 18c)
  • Overview of Large Page Support
    • Granting Lock Pages in Memory Privilege

1. From the Start menu, select Control Panel.
   The Control Panel window opens.

2. Double-click Administrative Tools.
   The Administrative Tools window opens.

3. Double-click Local Security Policy.
   The Local Security Policy window opens.

4. In the left pane of the Local Security Policy window, expand Local Policies and select User Rights Assignment.

5. In the right pane of the Local Security Policy window, double-click Lock pages in memory. The Lock pages in memory Properties window opens.

6. Click Add User or Group. The Select Users, Computers, Service Accounts, or Groups dialog box opens.

7. Enter Oracle Home User name in Enter the object names to select field and click Check Names.

8. Click OK to close the Select Users, Computers, Service Accounts, or Groups dialog box.
9. Click OK to close the Lock pages in memory Properties window.

Further reading

The document How to enable the "locked pages" feature in SQL Server 2012 (Microsoft Support) states:

_{(emphasis mine)}

Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. Memory that is allocated by using this method is never paged out by the operating system and is locked down until the application explicitly frees it or exits. The application requires the "Lock Pages In Memory" user right (LPIM) to be granted for the application to be able to lock pages in memory.

The SQL Server 64-bit version uses "locked pages" to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. The use of AWE APIs for memory management in 64-bit SQL Server is also frequently referred as "locked pages." You can enable the "locked pages" feature in SQL Server versions 2005, 2008, and 2008 R2 by using a combination of Windows user right, hotfix, and trace flags. The behavior is different, depending on the edition of SQL Server in these versions.

But

Apparently this is not required to be set if the service account is Local System.

Some who attended one of my talks at the recent PASS conference asked me the following question "Do I need to use the Group Policy Editor to assign the Lock Pages in Memory privilege if my SQL Server Service is running under the Local System Account?". The answer to this question is no and here is why. First let me explain, how this works for 64bit systems:

...[long explanation]

So after all of this (but I hope you found the details helpful) back to the original question and my conclusion. The Local System account has the 'lock pages in memory' privilege by default. For user accounts, you must grant the account this privilege explicitly.

Bob Ward, Microsoft

^{Reference: Do I have to assign the Lock Pages in Memory privilege for Local System? (Microsoft | Develop | CSS SQL Server Engineers)}

Questions

1. Seeing as the Microsoft Server (the LOCAL SYSTEM?) will reclaim memory, if the OS comes under pressure, will SQL Server and/or Oracle (have to) release memory to the system, because the service is running under LOCAL SYSTEM account, even though the Lock Pages In Memory is implicitly set?

If the answer to the 1. question is:
No, the services will not release memory, then my next question is:

2. Is this because the SQL Server OS / Oracle OS has requested that the pages be kept in memory and the LOCAL SYSTEM will accept this request?

If the answer to the 1. question is:
Yes, the services will release memory, then my next question is:

3. To explicitly Lock Pages in Memory is it better to run the services with Windows Accounts and grant them the Lock Pages in Memory privilege, so that the memory will be really locked?

Answer 1

[With LPIM] will SQL Server [...] releasememory to the system

Yes. SQL Server listens for a low memory notification from Windows and voluntarilly trims its caches in response. This process, being voluntary and asynchronous, does not guarantee that other processes won't have failed memory allocations. See Memory Management Architecture.

is it better to run the services with Windows Accounts and grant them the Lock Pages in Memory

This behavior does not depend on how the Service acquires the LPIM privilege, and for a host of other reasons you shouldn't use LOCAL SYSTEM as your SQL Server service account.

Answer 2

TL/DR:

Seeing as the Microsoft Server (the LOCAL SYSTEM?) will reclaim memory, if the OS comes under pressure, will SQL Server and/or Oracle (have to) release memory to the system, because the service is running under LOCAL SYSTEM account, even though the Lock Pages In Memory is implicitly set?

Don't know, I don't use the LOCAL SYSTEM account and you shouldn't either.

To explicitly Lock Pages in Memory is it better to run the services with Windows Accounts and grant them the Lock Pages in Memory privilege, so that the memory will be really locked?

Yes, use an AD account, and I would strongly recommend you look into Group Managed Service Accounts. They minimize so much of the administration overhead.

Longer answer (but just more info on LPIM, per my experiences):

In case you haven't already stumbled across this article, Great SQL Server Debates: Lock Pages in Memory by Jonathan Kehayias, I'd suggest you give it a read. It's older, but I still think it's quite relevant as the line in the sand for how Windows drastically changed it's memory management was Windows 2008 (e.g. prior to Windows 2008 it was terrible, but after it's become much better, in my jaded opinion).

With that being said, the environments that I have with LPIM enabled still do get trimmed by the OS and release memory, but they don't release large amounts of memory like on older versions of Windows. I may see 1 - 10 MB of memory allocation movement at a time, but nothing crazy. The key here though is to stabilize memory usage on the OS, so trim operations don't often occur. Jonathan's article, How much memory does my SQL Server actually need? does a good job of outlining how much SQL should be allocated and how much should be left for the OS to play with. I think the progression is a little more logorithmic than indicated in the article, so fine tune as necessary. The point here though is if you leave the proper amount for the OS, trim operations shouldn't happen all that often.

With newer versions of Windows (e.g. Windows 2012 and 2016), the necessity for LPIM isn't there, in my opinion, but it does change the way SQL Server allocates memory, which can affect workloads. As Joe Obbish points out some workloads may benefit from LPIM:

Lock pages in memory can have a dramatic effect on workload performance even when paging to disk is not occurring. This must be a gross oversimplification, but with LPIM SQL Server uses different OS calls to manage memory and the difference in those calls can lead to scalability issues with and without LPIM. For example, highly concurrent workloads that call HASHBYTES have been observed to get better throughput with LPIM.

Additionally, in a great session Joe presented at my local SQL Saturday, he did show that LPIM can also be effective in addressing RESERVED_MEMORY_ALLOCATION_EXT waits for certain workloads (pg. 126 of the slidedeck). I don't know if I would go so far as to say enabling it is a one-size-fits-all sort of solution here, as I have to believe there are downsides for other workloads as well.

Finally, as originally pointed out in Jonahan's Great SQL Server Debate paper, the way that Windows allocates memory differs when LPIM is enabled or not. The byproduct to this that I've seen is traditional memory usage reporting often is inaccurate. I'm often at odds with my VMWare admins as they don't think my SQL Server services are using all of the memory being allocated. I'd love to see some formal documentation on what ramifications LPIM has on reporting memory usage, but this is the only explanation I can come up with why their memory usage charts don't match my own.