SO what the heck is lock pages in memory? It enables SQL server to manage memory more efficiently!
To enable the lock pages in memory option.
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, add an account with privileges to run sqlservr.exe.
8.Log out and then log back in for this change to take effect.
Its user right for windows account and can be enabled by using secpol.msc or gpedit.msc
Why do we need this privilege for SQL Server startup account?
There are 3 different memory models in 64-bit SQL Server. They are conventional ,locked pages and large pages memory model.
Locked pages memory model: In lock pages memory mode SQL Server uses allocateuserphysicalpages and mapuserphysicalpages function to allocate memory. Caller token of this function should have LPIM privilege else the function call would fail, hence you need LPIM for startup account of SQL Server to use lock pages memory mode.
Large pages memory model: In large pages memory model I.e When you use TF834 in enterprise edition on systems with physical memory >8GB SQL Server uses large pages memory model. In this memory model SQL Server uses vitualalloc API with MEM_LARGE_PAGES allocation type. For using MEM_LARGE_PAGES in virtualalloc caller token must have LPIM privilege.
Memory allocated using AWE allocator API’s (or) Virtualalloc function with MEM_LARGE_PAGES are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon. process. Private bytes (or) Perfmon.process. working set.
In most cases, lock pages in memory should be enabled.
A good bit of this is at: