Saturday, October 26, 2013

2 Enable Lock Page in Memory (LPIM) for SQL Server 2012

SQL Server 2012 64-bit use AWE API for LPIM. Even AWE is no longer needed on the 64-bit because the VAS on the 64-bit is significant larger ( 8TB), however, SQL server engineer found to use the AWE API for LPIM could get the performance gain for SQL server memory allocation. ( .

When AWE enable ( in any version) , the memory that is allocated through AWE API would not be part of the working set memory hence, it is “LOCK” . This is the reason that LPIM privilege needs to be set on the SQL server account.

Grant the LPIM privilege

Add the SQL server account to the Lock Page in memory policy through gpedit.msc.


SQL Server error log





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_id <> 64
select large_page_allocations_kb,locked_page_allocations_kb
from sys.dm_os_process_memory



VMMAP would also show the locked Working set. the number is the same as the result we see in the DMV.


No More AWE in SQL 2012

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'awe enabled' does not exist, or it may be an advanced option.


Here is the screenshot from SQL Server 2012 from SSMS.


Here is the screenshot from SQL Server 2008R2 from SSMS





  1. Eminem is an American songwriter, rapper, record producer and an actor. Hе hаѕ also bееn dеѕсrіbеd аѕ оnе оf thе grеаtеѕt hір-hор аrtіѕtѕ оf аll tіmе, оnе оf thе grеаtеѕt rарреrѕ оf аll tіmе аnd was lіѕtеd іn Rоllіng Ѕtоnе lіѕt оf 100 grеаtеѕt аrtіѕtѕ оf аll tіmе аt number 83. Here are a few more interesting facts and Eminem net worth you might not have known.

  2. Along these lines, tell those companions who are now locked that you are making that change and that you will require their help.



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates