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...
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.
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!
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.
If the procedure was done correctly, the following message will appear at start up in the SQL Server instance's ERRORLOG: