During SQL Server 2012 RC0 setup (specifically when upgrading), you may have noticed upgrade rules regarding Lock Pages in Memory (LPIM):

However, for most folks, these rules always pass, whether or not they are actually using LPIM. I wanted to run a few tests to demonstrate why this is – or at least in which situations the rule checks will fail. So I created two virtual machines running SQL Server 2008 R2 SP1 CU3 – one running Windows Server 2008 SP2 (x86), the other running Windows Server 2008 R2 SP1 (x64 obviously). Both machines have 4 CPUs and 8GB of RAM, and have been updated with all Windows Updates (except Internet Explorer) as of the time of writing. I set up SQL Server to run as an explicit user account (to make it easy to assign LPIM rights for the appropriate tests).

I will attempt to upgrade SQL Server under various scenarios (LPIM / AWE on x86, and LPIM / trace flag 845 on x64 – required for LPIM on Standard Edition). All of these tests will be run with LPIM enabled, but other settings will vary as follows:

On the 2008 box, to make sure that AWE can be used by SQL Server, you may first need to set this at the operating system level – this depends on the specific operating system, whether DEP is enabled, amount of RAM and other factors. It used to be quite easy – go into boot.ini, add the /PAE switch, and reboot. Starting with Windows Server 2008, this changed to use the command-line utility bcdedit. To ensure that AWE and PAE are explicitly set on this specific system (x86 box with 8GB RAM), you could run the following command at an elevated command prompt:

bcdedit /set pae ForceEnable

Of course we don't have to do any of this on the x64 box – and in my case I didn't need to do this on the x86 box, either. But the matrix for the above can become quite complicated, and is just one more reason to get off of x86 – both Windows and SQL Server – as soon as you can! (Never mind that AWE is no longer supported in SQL Server 2012, nor is a cluster on WoW.)

Next, to set Lock Pages in Memory, we need to launch the Local Group Policy Editor (Start > Run > gpedit.msc) and add the SQL Server service account to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment > Lock pages in memory:

And then set SQL Server to run as the matching account so that it inherits the LPIM rights. You can always change this later, but for a one-off test like this it can be just as easy to specify the service account during setup:

We can then enable trace flag 845 using SQL Server Configuration Manager – right click the appropriate service, choose Properties, and on the Advanced tab, add -T845 to the Startup Parameters list (make sure there is no space between the last ; and -T845):

Note that this is much easier with SQL Server 2012 client tools installed, even against older versions of SQL Server, as there is a new Startup Parameters tab to help you avoid messy typing inside that little combo box:

And finally, to enable SQL Server to utilize AWE on the x86 box, you can use the UI through Management Studio, or you can run the following code using sp_configure:

 EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
 
EXEC sp_configure 'awe enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO
 
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
GO

(This is all just for testing purposes, but of course I need to point out that you wouldn't just enable AWE or LPIM without also adjusting min/max server memory appropriately and without proper consideration into what other services on the box will need memory.)

Restart each box and we should be ready for the first tests. We can verify LPIM / AWE via the following entries the SQL Server error log after restart:

For x64, there will be a line item that says "Using locked pages for buffer pool.":

When LPIM is not enabled (e.g. when the user rights assignment has not been set or the trace flag is not in use), the line above does not appear.

For x86, a message like "Using locked pages for buffer pool." will not appear in the error log, even though LPIM is enabled in this case. Instead you will see a line item about AWE, "Address Windowing Extensions is enabled":

 

One issue you might come across is the case where you've triple-checked everything – you know you've given the service account LPIM rights in the Group Policy editor, you've set the trace flag and you've restarted SQL Server, but the error log still seems to indicate that LPIM is not set. Please verify that you've entered the trace flag argument correctly. The following two entries are not the same, and only the first is valid:

 ...\mastlog.ldf;-T845;
 
...\mastlog.ldf; -T845;
----------------^  this space may make you scream

Once you've verified that LPIM is up and running, let's go through the first few steps of SQL Server 2012 RC0 setup and see how the rule checks pan out.

32-bit systems

1. AWE and trace flag 845 both enabled

   If you have AWE enabled and the trace flag turned on, the AWE check fails:

    

   —————————
   Rule Check Result
   —————————
   Rule "Is AWE Enabled Check for x86 installations" failed.

   AWE is currently enabled on a 32-bit instance of SQL Server. This feature is no longer supported.
   You must disable AWE before upgrading the instance.
   —————————
   OK  
   —————————