As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to show up in the SQL Server log. I want to make sure I can do this correctly before I do it for a client.
Our development server is a Windows 2003 with 4 GB of RAM, SQL Server 2008 R2 Standard Edition 32Bit.
Below is what I have attempted:
-
Checked "Use AWE to allocate memory".
-
Set the "Maximum server memory"
setting. -
Added startup parameter -T845 in the
SQL Server service configuration. -
The SQL Server service is running on
the LocalSystem Account so from what
I have read I do not need to enter an
account in the "Lock Pages in Memory"
priveledge. However, I did try
changing the service to operate under
the "Administrator" login, and added
that login to the "Lock Pages in
Memory" privelege. -
Restarted SQL Server Service.
Nothing I have done will put
Using locked pages for buffer pool
into my SQL Server log, but the 845 trace appears to be turned on when I run DBCC TRACESTATUS.
Anyone have any ideas about why nothing I have done appears to turn this on?
Best Answer
As I see, you're using a 32 bit system, so please follow the information from this MSDN article: Do I have to assign the Lock Pages in Memory privilege for Local System?. It's from MS's own CSS team.
Quote from it: "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. "
Another detailed article regarding "Lock pages in memory" is from well known sql writer Glenn Berry: SQL Server and the “Lock pages in memory” Right in Windows Server.