Saturday, October 26, 2013

0 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. ( http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx) .

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.

image

SQL Server error log

 

image

DMV

 

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

image

VMMAP

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

image

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.

image

Here is the screenshot from SQL Server 2012 from SSMS.

image

Here is the screenshot from SQL Server 2008R2 from SSMS

image

 

Reference

0 comments:

Post a Comment

 

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