How to: Enable the Lock Pages in Memory Option (Windows)
The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server. You must be a system administrator to change this policy.
For a table that lists the maximum server memory values, see Memory Architecture.
To enable the lock pages in memory option
On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add.
In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.
- 2/8/2011
- Amit Banerjee
I've always wondered is it better to add each explicit service account to have the Lock Pages in Memory user right or to add the SQL service account groups.
Did you know that when you install SQL Server 2005 or 2008 that a local group is created? This is very helpful for administration, since instead of adding permissions on one single account that might be changed one day, SQL adds the proper permissions to the group, and whenever you need to change the service account in Configuration Manager, it puts the new service account into the group so the account inherits the needed security from the group itself.
On a clustered installations, the security group used for the SQL service accounts is a domain level group, and you explictly had to specify the domain group, so it wasn't one of the automatic local groups since those don't get created on clusters.
For more info on these helpful Security groups, see also http://msdn.microsoft.com/en-us/library/ms143504.aspx
Therefore, consider the implications of this "Lock Pages" article - I think making the local group have this right is better long term because you don't have to worry about a service account change leading to memory changes.
In SQL Server 2005 each instance will have a separate group for Engine and AS features:
A. For Database Engine consider adding this local group to have the user right:
SQLServer2005MSSQLUser$yourservername$yourinstancename
B. For Analysis Services (Preallocate uses lock pages rights) consider adding this local group to have the user right:
SQLServer2005MSOLAPUser$yourservername$yourinstancename
* yourinstancename is MSSQLSERVER for a default instance
In SQL Server 2008 each instance will have a separate group for Engine and AS features:
> For Database Engine consider adding this local group to have the user right:
SQLServerMSSQLUser$yourservername$yourinstancename
> For Analysis Services (Preallocate uses lock pages rights) consider adding this local group to have the user right:
SQLServerMSASUser$yourservername$yourinstancename
* yourinstancename is MSSQLSERVER for a default instance
Consider also - If you are in a domain where the same service account is used on many machines to run SQL and Analysis Services, then editing the group policy at domain level since that may be easier to implement widely across many machine rather than the local policy perhaps.
Thx, Jason
- 8/20/2010
- Jason H - SQL