How to enable the "Lock pages in memory" security policy.

Peter Schmitz

Administrator
Staff member
In the 64 bit version of SQL Server, to use several gigabytes of RAM, one does not have to turn on AWE like in the 32 bit version of SQL Server. However, there is a setting that does have to be changed in order to take full advantage of this RAM that is potentially available to SQL Server, and that is the "Lock pages in memory" setting.

In short, this setting allows SQL Server to internally decide which amount of RAM it uses will be released to the OS in case the OS requires more RAM, rather then letting the OS decide this for SQL Server. SQL Server will be able to make a more educates guess at which RAM is good to release than the OS can, and thus you will see better performance with this setting enabled.

To turn the setting on, click Start -> Run, and then type 'gpedit.msc' (without the quotes) to fire up the Group Policy Editor. In it, under the "Computer Configuration" options, expand "Windows Settings". Under that, expand "Security Setttings", and under that, expand "Local Policies". Then click the "User Rights Assignment" folder.

LockPages1.png

In the details screen to the right, look for the "Lock pages in memory" setting, and either double-click it, or right-click it and then select "Properties" to bring up the Local Security Policy Setting window.

In it, click the "Add User or Group" button, and enter the credentials of the user running the SQL Server process.

LockPages2.png

Click "Ok", and you're done.
 
Top