How to enable the "locked pages" feature in SQL Server 2012

Article translations Article translations
Article ID: 2659143 - View products that this article applies to.
Expand all | Collapse all

SUMMARY

Windows-based applications can use Windows AWE (Address Windowing Extensions) APIs to allocate and to map physical memory into the process address space. Memory that is allocated by using this method is never paged out by the operating system and is locked down until the application explicitly frees it or exits. The application requires the "Lock Pages In Memory" user right (LPIM) to be granted for the application to be able to lock pages in memory.

The SQL Server 64-bit version uses "locked pages" to prevent the process working set (committed memory) from being paged out or trimmed by the operating system. The use of AWE APIs for memory management in 64-bit SQL Server is also frequently referred as "locked pages." You can enable the "locked pages" feature in SQL Server versions 2005, 2008, and 2008 R2 by using a combination of Windows user right, hotfix, and trace flags. The behavior is different, depending on the edition of SQL Server in these versions.

SQL Server 32-bit versions 2005, 2008, and 2008 R2 use "locked pages" when the "awe enabled" SQL Server feature is enabled.

Starting with SQL Server 2012, the memory manager simplifies the usage of "locked pages" across supported editions and processor architectures. In SQL Server 2012, you can enable "locked pages" by granting the "lock pages in memory" user right for the startup account for the instance of SQL Server in Windows. This is applicable for Standard, Business Intelligence, and Enterprise editions of SQL Server 2012 running on both 32-bit and 64-bit processor architectures.

MORE INFORMATION

The following table provides the requirements to enable "locked pages" in different versions and editions of SQL Server on different platforms:
Collapse this tableExpand this table
Enterprise Edition,
Data Center Edition
Standard Edition,
Business Intelligence Edition
Other editions
SQL 2012 (32-bit and 64-bit)• Assign LPIM user right to SQL Startup account• Assign LPIM user right to SQL Startup accountNo support for "locked pages"
SQL Server 2005/2008/2008R2 (32-bit)• Assign LPIM user right to SQL Startup account
• Configure 'awe enabled' option to 1
• Assign LPIM user right to SQL Startup account
• Configure 'awe enabled' option to 1
No support for "locked pages"
SQL Server 2005/2008/2008R2 (64-bit)• Assign LPIM user right to SQL Startup account• Assign LPIM user right to SQL Startup account
• Apply SQL Hotfix KBA 970070
• Enable trace flag 845
No support for "locked pages"
The LPIM user right refers to the "Lock Pages in Memory" Windows user right. This user right should be assigned to the SQL Server service startup account.

You do not have to use Trace Flag 845 in SQL Server 2012. You can remove this trace flag from the startup parameters after you upgrade your instance to SQL 2012.

Even though the "awe enabled" feature is not available in 32-bit SQL Server 2012, you can still use the "locked pages" feature by assigning the "lock pages in memory" user right for the SQL Server startup account.

When you enable "locked pages," it is very important to set an appropriate value for "max server memory" and for "min server memory" configuration options for each instance of SQL Server to avoid system-wide problems.

Use the following methods to determine whether the SQL Server 2012 instance is using "locked pages":
  • The output of the following TSQL query will indicate nonzero values for locked_page_allocations_kb:

    select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
    from sys.dm_os_memory_nodes omn
    inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)
    where osn.node_state_desc <> 'ONLINE DAC'
  • The current SQL Server error log will report the following message during server startup:
    Using locked pages in the memory manager
  • The "Memory Manager" section of the DBCC MEMORYSTATUS output will show a nonzero value for the "AWE Allocated" item.The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.
The following table provides more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product against which the rule is evaluated.

Collapse this tableExpand this table
Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated
SQL Server setup Upgrade RulesLPIM check for x64 installationsConsider the following scenario on an X64 system:
  • You install SQL Server [2008 R2, 2008, 2005] standard edition.
  • You grant the "Lock Pages in Memory" user right to the SQL Server service startup account.
  • You did not enable the trace flag 845, as a result the SQL Server instance did not use locked page allocations.
  • Now you attempt to upgrade this SQL Server instance to SQL Server 2012.
  • Now the upgraded SQL Server instance starts using locked page allocations.

This upgrade rule warns you about this change in behavior.
SQL Server 2008
SQL Server 2008 R2
SQL Server setup Upgrade RulesLPIM check for x86 installationsConsider the following scenario on an X86 system:
  • You install SQL Server [2008 R2, 2008, 2005] standard edition.
  • You grant the "Lock Pages in Memory" user right to the SQL Server service startup account.
  • You did not setup and configure the ‘awe enabled’ feature, as a result the SQL Server instance did not use locked pages allocations.
  • Now you attempt to upgrade this SQL Server instance to SQL Server 2012.
  • Now the upgraded SQL Server instance starts using locked page allocations.
This upgrade rule warns you about this change in behavior.
SQL Server 2008
SQL Server 2008 R2




Note A bug condition could prevent these rules from providing the appropriate warnings. Please see the note:

http://blogs.msdn.com/b/psssql/archive/2012/04/30/clarification-about-the-two-lpim-upgrade-rules-that-did-not-fail.aspx

REFERENCES

How to: Enable the Lock Pages in Memory Option (Windows)

The "awe enabled" SQL Server feature is deprecated

Support for Locked Pages on SQL Server 2008 R2 Standard Edition x64, on SQL Server 2005 Standard Edition 64-bit systems, and on SQL Server 2008 Standard Edition 64-bit systems

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

SQL Server memory models

Properties

Article ID: 2659143 - Last Review: July 9, 2012 - Revision: 4.0
APPLIES TO
  • Microsoft SQL Server 2012 Enterprise
Keywords: 
kbtshoot KB2659143

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com