Consideration
This setting doesn’t get enough credit for all the good it does. Part of me thinks it should be the default for new SQL Server installs, if the amount of memory in the server is over a certain number, and max server memory is set to match.
You may not want it turned on only based on physical memory, because there are lunatics out there who stack SQL Server installs, and who install all sorts of other dimwitted things on there, too.
But since max server memory is a suggestion during setup, and perform volume maintenance tasks is included, this should be as well.
Again, it’s one less post-install step for automation-minded folks out there.
Burden
SQL Servers with large amounts of memory can especially benefit from this setting, because it allows them to access memory via a different API. The easy way to think of it is that SQL Server will get direct access to physical memory, instead of virtual memory.
Allocates physical memory pages to be mapped and unmapped within any Address Windowing Extensions (AWE) region of a specified process.
The AllocateUserPhysicalPages function is used to allocate physical memory that can later be mapped within the virtual address space of the process. The SeLockMemoryPrivilege privilege must be enabled in the caller’s token or the function will fail with ERROR_PRIVILEGE_NOT_HELD.
I generally advise people with good chunks of memory to enable this setting. There are very few good reasons not to on big boxes, and that’s why it should be called out in the installation process. Enabling it later means rebooting, and that sucks.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
A very controversial theme, if I may, a lot of spooky tales around! And no good posts I can find about LPIM pros and contras that goes down to business. As an old-time cargo cultist, for me it was all about stable locked RAM for buffer pool plus risking hard page faults of SQL Server internal stuff (cos it still allocates non-locked pages for some of these even with LPIM turned on) in case of heavy external memory pressure, leaks, whatever. But Mr. Bob Ward agrees with you in his presentations, so maybe I should reconsider.
No one ever seems to have a good reason from recent history 🤷♂️
What do you consider “large amounts of memory” for this purpose, assuming just one instance on a VM? Azure Assessment (Preview) is giving me a high priority to recommendation to turn this on, even in an instance on an Azure VM with only 14 GB of RAM. Where do you feel the line is between “not enough RAM – leave it off” and “large amounts of RAM – turn it on”?
I’d say anything that is equal to or surpasses the buffer pool limit for Standard Edition.