Do I have to assign the Lock Pages in Memory privilege for Local System?
NOTE: I've updated this because of some questions about 32bit systems.
Some who attended one of my talks at the recent PASS conference asked me the following question "Do I need to use the Group Policy Editor to assign the Lock Pages in Memory privilege if my SQL Server Service is running under the Local System Account?". The answer to this question is no and here is why. First let me explain, how this works for 64bit systems:
If you want to determine whether SQL Server 2005 64bit systems are actually taking advantage of the Lock Pages in Memory privilege, look for the following entry in your SQL ERRORLOG when it is starting up:
Using locked pages for buffer pool
If you don't see this message, the policy was not set correctly or set for the wrong account (or you are not using Enterprise Edition). If you do see this message, then SQL Server recognizes this privilege for the account associated with the SQL Server Service.
So the next logical question is how does SQL Server decide to print this message? Well here is the algorithm:
1) Call the AdjustTokenPrivileges() API to enable the SeLockMemoryPrivilege based on the current process access token.
2) Call GetLastError() to see if this API was successful.
3) If this call was successful and the SKU is Enterprise Edition, we print the message in the ERRORLOG and we take advantage of this privilege.
On my Windows 2003 Enterprise Edition SP2 Server, I ran the Group Policy Object Editor as described at http://msdn2.microsoft.com/en-us/library/ms190730.aspx. On my system, no users had this privilege and to no surprise this entry was not in my ERRORLOG. I then added the domain account to this privilege associated with the SQL Server service using the policy editor and restarted SQL Server. Now this entry was in my ERRORLOG. I then changed the logon account for my SQL Server service to Local System and restarted SQL Server. The entry remained in my ERRORLOG. But the the Group Policy Object Editor does not show the SYSTEM account in its list of users who have the Locked Pages in Memory privilege.
If you are using 32bit systems, the messages and algorithm are slightly different. The message you should look for in the ERRORLOG is the following:
Address Windowing Extensions is enabled
If the privilege was not set correctly, you will see this message:
Could not use Address Windowing Extensions because the 'lock pages in memory' privilege was not granted.
These messages will NOT show up for 32bit systems unless the configuration value 'awe enabled' is set to 1. So the algorithm for 32bit systems is:
- If 'awe enabled' = 0 but there is enough physical memory that AWE might help you (more on that at later), then print the message
Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
Otherwise, don't print any message
- Else If 'awe enabled' = 1 and the "lock pages in memory check" as above is TRUE, then print the message that AWE is enabled.
- Else if 'awe enabled' = 1 and the "lock pages in memory check" as above is FALSE, then print the message that AWE cannot be enabled.
The message "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory" is printed if the size of the Virtual Address space is < the amount of physical memory on the machine (or the 'max server memory' option). So on a 32bit machine where /3GB is being used, if the machine has 4Gb of physical RAM, we will print this message if 'awe enabled' = 0.
A question to ask here is why do you need to enable AWE for 32bit systems to use lock pages in memory privilege?That question is actually the wrong one to ask but helps explain a few things. For 32bit systems, the main reason we designed SQL Server to use the AWE APIs is to access more memory than its virtual address space will allow (>4Gb). But to use the AWE APIs, the 'lock pages in memory' privilege must be set.
For 64bit systems, the virtual address space limitation is not an issue. However, if an application wants to "lock its memory" or avoid its working set from being trimmed, then it can achieve this by using the AWE APIs. Again, in order to use these APIs, you must have the 'lock pages in memory' privilege set. This is why for 64bit systems, you don't need to set the 'awe enabled' option to 1 (it is actually ignored on 64bit systems).
So, the purpose of AWE for 32bit systems was to access more memory. But for 64bit, AWE APIs are used to avoid a working set trim. So SQL Server will automatically use the AWE APIs provided the 'lock pages in memory' privilege is set (and your are using Enterprise Edition). If you want to read more about the AWE APIs, look here in MSDN: http://msdn2.microsoft.com/en-us/library/aa366527.aspx
So after all of this (but I hope you found the details helpful) back to the original question and my conclusion. The Local System account has the 'lock pages in memory' privilege by default. For user accounts, you must grant the account this privilege explicitly.
Bob Ward, Microsoft
