CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 15

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

http://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 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. 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: 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

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • PingBack from http://www.artofbam.com/wordpress/?p=10141

  • I was trying to get AWE and Database Mail enabled on a x64, 2005 box.  The machine was set up to use the NetworkService account, and I couldn't get AWE enabled without enabling the lock pages in memory setting.  What was doubly odd was that none of the other sp_configure settings that I changed took effect prior to enabling that.  RECONFIGURE would say that the Lock Pages In Memory had to be set first, and wouldn't enable mail until that was done.  

    Just found that terribly odd.

  • 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

  • my 64bit says "Using locked pages for buffer pool"..

    but cant find the message in 32bit however it says address windowsing extension..is enabled......

    is it only applies to 64bit?

    This is a great question and has caused me to go back and look at how we print this messagei in the ERRORLOG. I'm going to update the blog with new information that covers your questions and sets a few other facts straight.

    bob ward

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

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

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

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

  • View products that this article applies to. On This Page SUMMARY SYMPTOMS CAUSE WORKAROUND MORE INFORMATION

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

  • Assumptions : Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL

  • "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

  • 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.

  • Thanks Bob for this wonderful article

  • 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.

Page 1 of 1 (15 items)