Lock pages in memory - AWS Prescriptive Guidance

Lock pages in memory

Enable the Lock pages in memory option for the SQL Server startup account to ensure that the operating system doesn’t trim the SQL Server working set.

To check whether this option is enabled, use the following SQL query:

SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;

Output:

sql_memory_model     sql_memory_model_desc
1	                  CONVENTIONAL

"CONVENTIONAL" means it’s not enabled.
To enable the Lock pages in memory option:

  1. On the Start screen, run secpol.msc to open the Local Security Policy console.

  2. Choose Local Policies, User Rights Assignment, Lock pages in memory, and add the SQL Server service account, as shown in the following screenshot.

    Enabling the lock pages in memory option

  3. Restart the SQL Server instance for changes to take effect.

  4. Use the following SQL query to confirm that the Lock pages in memory option is enabled:

    SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;

    Output:

    sql_memory_model     sql_memory_model_desc 
2                    LOCK_PAGES

"LOCK_PAGES" means it’s enabled.

For more information about the SQL Server memory model, see sql_memory_model and sql_memory_model_desc in the sys.dm_os_sys_info documentation on the Microsoft website.