Memory tweaks for your 32-bit SQL Server data platform, mind the gap: Lock Pages in Memory

Few months ago I blogged about importance of trace flat 845 and required settings on Memory when you have 32-bit environment or mixture of both 32-bit & 64-bit software on same hardware.

No doubt that the physical resources are managed differently between 32-bit & 64-bit, similarly since SQL Server version 2005 the local security policy right for Lock Pages in Memory is highly essential for the SQL Server service account. As you may be aware it was only available in Enterprise and Developer editions, since CU4 for SQL Server 2005 SP3 and CU2 for SQL Server 2008 SP1 it is enabled as a trace flag 845 for Standard Edition environment too.

Going back to basics, this policy right: Lock Pages in Memory policy is required to enable AWE for SQL Server engine, which is applies to only 32-bit and Analysis Services cannot take advantage of AWE and also if the available physical memory is less than user mode VAS then AWE cannot be enabled. Although this is not required in 64-bit systems it is ideal to enable on these kind of environments too to optimize system memory use for SQL Server. The Lock Pages in Memory policy determines which account can use the process to keep the data in memory (physical) preventing the system from paging the data to virtual memory on the disk, by default this is OFF.

By default this policy is OFF and it can be set using Windows Group Policy tool (GPEDIT.MSC) from operating system (Start –> Run) and the rights must be assigned t the account that is used to start SQL Server services. As per BOL to enable the Lock Pages in Memory option, see How to enable “Lock pages in Memory” at the Windows level:

  • On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.
  • On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
  • Expand Security Settings, and then expand Local Policies.
  • Select the User Rights Assignment folder. The policies will be displayed in the details pane.
  • In the pane, double-click Lock pages in memory.
  • In the Local Security Policy Setting dialog box, click Add.
  • In the Select Users or Groups dialog box, add the account that runs the SQL Server Service.
  • Restart the machine for these changes to take effect.

As referred above this is available for Enterprise edition (production) and Developer editions, for Standard Edition systems you need to use Trace Flag 845 as a startup trace flag. Here are the steps to follow in such cases:

  • Obtain the current build number of SQL Server using SELECT @@VERSION command and ensure that it is 10.00.2714.00 (SQL Server 2008) and/or 9.00.4226 (SQL Server 2005).
  • Then set the trace flag 845 as startup trace flag using SQL Server Configuration Manager, obviously you must restart SQL Server services to affect this change.
  • If in case the existing SQL Server build number is lower than that are specified above you will not be able to set this trace flag, that may result failure of starting specific SQL Server services.
  • For this reason the Cumulative Update packages have provided this feature, however if the SQL Server 2005 is patched upto Service Pack 4 (SP4) or SQL Server 2008 SP2 then you can follow the above steps.
  • To ensure whether the change is affected you will need to observe Using locked pages for buffer pool entry in SQL Server error log once it is restarted.

Further for SQL Server 2008 R2 Standard Edition (64-bit) also requires trace flag 845 to be added as a startup parameter so that SQL Server can use locked pages for the Buffer Pool when the SQL Server service account is granted the Lock Pages in Memory security privilege.

Nevertheless, it is essential to test these settings on Development environment thorougly before you proceed on Production instance as these changes on Standard Edition may produce negative performance affect due to the changes to the system settings for the services.

Also it is best to go through the information such as:

In some occassions you may get negative performance in 64-bit environment too so better to read on about How to reduce paging of buffer pool memory in the 64-bit version of SQL Server and options on SQL Server Working Set Trim Problems? – Consider.... A good insight about monday morning mistakes from SQLChicken.com.

Finally the information about support on this trace flag and policy setting go through Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems link.