Upgrading to SQL Server 2012 with Lock Pages in Memory
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
- 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
—————————
If you disable AWE and restart the service, you will see the above message about using sp_configure in the error log, but both the AWE and the LPIM checks pass. This is because you didn't already have LPIM enabled, since AWE was not enabled. Even though you might have thought that you were using LPIM prior to the upgrade:
—————————
Rule Check Result
—————————
Rule "LPIM Check for x86 installations" passed.Lock Pages In Memory (LPIM) check for X86 succeeded.
—————————
OK
—————————
If you re-enable AWE and then turn off the 845 trace flag, you won't see any error messages in the error log, except for the "AWE is enabled" message. And you will see the same AWE rule fail upgrade you'll see if you have the trace flag turned on:
—————————
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
—————————
If you then disable AWE and leave the trace flag disabled, you get the message in the log about not using all of the available memory, but again, like in case 2., both the AWE and LPIM upgrade rule checks pass:
—————————
Rule Check Result
—————————
Rule "LPIM Check for x86 installations" passed.Lock Pages In Memory (LPIM) check for X86 succeeded.
—————————
OK
—————————
This problem is now fixed in the following update:
2708594 FIX: Locked page allocations are enabled without any warning after you upgrade to SQL Server 2012
http://support.microsoft.com/kb/2708594/EN-US
Microsoft just published an update:
http://blogs.msdn.com/b/psssql/archive/2012/04/30/clarification-about-the-two-lpim-upgrade-rules-that-did-not-fail.aspx
Aaron
I posted a blog on the reasons that contribute to these rules not working. You can find them at http://blogs.msdn.com/b/psssql/archive/2012/04/30/clarification-about-the-two-lpim-upgrade-rules-that-did-not-fail.aspx
Thanks for reporting this.
Suresh Kandoth
Microsoft