I noticed there's many conflicting information on the internet about the need for page locking and its required privileges (Lock page in memory) for the instance's service account for 64-bit SQL Server. As part of a SQL Server health check, I decided to look more closely into the matter and came up with the conclusion that setting the page locking privilege for the SQL Server instance's service account is indeed still important, despite many other sources claiming otherwise. And here's why...

The 32-bit era...

In the past, during the 32-bit era, a single process was limited to a maximum of 4GB of memory. This would become a problem with databases which would often require more than that in order to maintain reasonable performance. To circumvent these limitations, Microsoft has developed Address Windowing Extensions. This API allowed 32-bit processes to use more memory (even beyond the upper 4 GB limit per process). One of Microsoft's products using this new API was SQL Server. Enabling AWE for a SQL Server instance allowed for SQL Server to allocate more memory, greatly improving performance due to the process being able to use more memory.

In order to allow a SQL Server instance to use AWE, the service account running the instance required a particular privilege, called Lock pages in memory.

What about 64-bit?

With the introduction of 64-bit, the memory limitations were removed, and AWE became obsolete. As a result, the privilege is usually overlooked, and certain sources on the internet claim it is no longer necessary, or even recommend against enabling it. They are wrong!

Aside from allowing 32-bit processes to use more memory, AWE also introduces the ability to lock pages in memory (as the privilege says), preventing memory pages from being swapped out to disk. This allows a process to prevent its working set from being trimmed or pages being swapped out to disk if the OS wants to. Because swapping to disk usually incurs a heavy penalty on performance, you can imagine the gain if memory pages of the SQL Server process cannot be swapped out.

As a result, it is still very much important to enable the Lock pages in memory privilege for the service account running the SQL Server instance, even on 64-bit!

Enable page locking (assigning the privilege)

Assign the Lock pages in memory privilege to the service account of a SQL Server instance, using a policy (can be local). Note that you do not have to assign the privilege if the instance is running as LOCAL SYSTEM.

1. Start gpedit.msc.
2. Navigate the tree:
   Computer Configuration → Windows Settings → Security Settings → Local Policies → User Rights Assignment
3. Find and double-click Lock pages in memory. In the dialog that follows click Add User or Group.
4. Search for and select the user running the sqlservr.exe process. Click OK to confirm the addition.
5. Restart the SQL Server service.

If the procedure was done correctly, the following message will appear at start up in the SQL Server instance's ERRORLOG:

ERRORLOG

Using locked pages for buffer pool