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.
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!
- Software Vendor Mistakes With SQL Server: Misusing Common Table Expressions
- Software Vendor Mistakes With SQL Server: Indexing #Temp Tables Incorrectly
- Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables
- Software Vendor Mistakes With SQL Server: Not Using #Temp Tables