Upgrading to SQL Server 2012 with Lock Pages in Memory

AaronBertrand — Mon, 06 Feb 2012 20:36:00 GMT

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

On x86 if LPIM is not enabled (e.g. if you haven't used the trace flag on Standard Edition, have not enabled AWE, or have not set the user rights correctly), you will see different error messages in the log, such as "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." and "Addressing Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.":

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

AWE disabled, trace flag 845 enabled

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

AWE enabled, trace flag 845 disabled

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

AWE and trace flag 845 both disabled

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

So for x86, I found no way to trigger an upgrade rule failure for the specific "LPIM Check for x86 installations" using RC0, but perhaps by RTM, this rule will also fail in addition to the AWE check in cases 1. and 3. above. In any case, I don't think it's that big of a deal since, if you are running x86, you won't be able to take advantage of AWE or LPIM anyway (more on that below). I think during the upgrade process you should be warned about this on any x86 machine with more than 4GB of RAM, regardless of your current AWE settings or trace flags. Not everyone is going to read the discontinued engine features topic or attend one of my "What's New in SQL Server 2012?" presentations, and they may not realize later that when they change those settings they are not going to have any effect.

Note that in SQL Server 2012, AWE is no longer supported. So it may very well be the case that you were expecting your AWE settings (in sp_configure, not in boot.ini) and LPIM to play well together after upgrading to SQL Server 2012, but this is not the case - SQL Server will no longer be able to see all of your memory, and this may have significant impact on your workload. Of course if you have a machine with 16GB of RAM and suddenly SQL Server will only see about 4GB, you might not be too impressed with the "upgrade" - hence the warnings. Note also that your experience with the RCs of SQL Server 2012 (all of which are Evaluation Edition) may yield different results than what will happen at RTM time (when you will be able to use a license key to specify "normal" editions like Standard and perform an actual end-to-end upgrade).

64-bit systems

Trace flag 845 enabled

As expected, with both LPIM and TF845 enabled, the upgrade rule "LPIM Check for x64 Installations" passes (even though the "Using locked pages in memory" message does not appear in the error log). There is nothing to warn the user about because LPIM will continue to be honored:

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x64 installations" passed.

Lock Pages In Memory (LPIM) check for X64 succeeded.
---------------------------
OK
---------------------------

Trace flag 845 disabled

If we restart SQL Server without the trace flag, we no longer see the "Using locked pages for buffer pool" message in the error log, but the LPIM check still succeeds - even though we're on Standard Edition and the trace flag is not set (which is the reason behind the warning):

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x64 installations" passed.

Lock Pages In Memory (LPIM) check for X64 succeeded.
---------------------------
OK
---------------------------

This last case is the most concerning to me. The point of the warning is to let me know that I have the LPIM permission set for the service account, but that it won't be honored without the trace flag because I'm running Standard Edition. In this specific case, I think the LPIM upgrade rule should fail, even though my current configuration also prevents me from using LPIM.

Conclusion

I quite sincerely hope this is my last 32-bit related blog post. For one, the x86 installation of SQL Server took at least five times longer than the x64 equivalent. x86 RIP!

In any case, this is just one way Microsoft is helping to prevent customers from shooting off their own feet. Especially in the x86 case where their current workloads are dependent upon AWE; it needs to be clear to them that after the upgrade to SQL Server 2012, their configuration isn't what they think it is...

Search results matching tags 'SQL Server 2012', 'x64', 'awe', and 'trace flags'

Upgrading to SQL Server 2012 with Lock Pages in Memory