Do I have to assign the Lock Pages in Memory privilege for Local System?

  • [アーティクル]

IMPORTANT: The following KB article adds support for Locked Pages to SQL Server Standard Edition

https://support.microsoft.com/kb/970070

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 https://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. The second message can show up if the "Lock Pages in Memory" privilge was changed after successfully configuring AWE and then the privilige was cleared OR you used RECONFIGURE WITH OVERRIDE and the privilige was not set. 

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.

Note that in SQL 2008 this message changed to

SQL Server is not configured to use all of the available system memory. To enable SQL Server to use more memory, set the awe enabled option to 1 by using the sp_configure stored procedure.

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: https://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

Comments

  • Anonymous
    October 18, 2007
    PingBack from http://www.artofbam.com/wordpress/?p=10141

  • Anonymous
    October 20, 2007
    The comment has been removed

  • Anonymous
    October 23, 2007
    i have lock pages in memory set in gpedit.. however i cannot locate this messsage in sql errorlog (Log File Viewer in SSMS or ERRORLOG files) "Using locked pages for buffer pool".. ??? Are you positive that "lock page in memory" privilege is set for the account that that the SQL Server Service is logged on with? Also, this only for Enterprise Edition. bob ward

  • Anonymous
    October 23, 2007
    The comment has been removed

  • Anonymous
    January 06, 2008
    There is an almost constant stream of posts on forums asking about configuration of SQL Server 2005 memory,

  • Anonymous
    January 24, 2008
    A lot of customers have been asking me if they need to use Enterprise Edition. the basic answer is that

  • Anonymous
    March 12, 2008
    Welcome to the Dynamics Ax Performance Team's blog. We're putting together a team introduction and hope

  • Anonymous
    March 30, 2008
    A lot of customers have been asking me if they need to use Enterprise Edition. the basic answer is that

  • Anonymous
    February 25, 2009
    View products that this article applies to. On This Page SUMMARY SYMPTOMS CAUSE WORKAROUND MORE INFORMATION

  • Anonymous
    March 31, 2009
    You might have seen these two messages logged in the SQL Server ERRORLOG files: - Cannot use Large Page

  • Anonymous
    June 16, 2009
    Assumptions : Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL

  • Anonymous
    April 11, 2010
    "Set AWE Enabled to 1 in the configuration parameters to allow use of more memory" Dont get why this message appears if I have 32 bit machine with 4GB and /3GB ? . ie why do I enable AWE to take advantage of memory <=4GB

  • Anonymous
    August 29, 2012
    Hi Guys, I have configured AWE option in sp_configure in SQL Server 2005. But i'm not able to add / remove the user in Lock pages in memory even after giving the full rights to the user. Add user/group button is always disables. Can anyone pls suggest wat has to be done to enable it.

  • Anonymous
    October 15, 2013
    Thanks Bob for this wonderful article

  • Anonymous
    October 16, 2013
    Thanks. I was surprised because all users was removed from the policy but SQL still using lock pages in memory. So, if Local System have this right by default, lets try changing the user for this service.

  • Anonymous
    May 04, 2017
    Wonderful website. Lots of helpful info here. I am sending it to several pals ans also sharing in delicious.And naturally, thanks on your effort!