SQL Server 2012:How to enable the Lock Pages in Memory Option

In this article I will discuss the feature “lock pages in memory” and demonstrate how we can enable the “Lock pages in Memory” option for SQL Server 2012 on the OS. The OS decides which accounts can utilize a process to retain data in the RAM. This would not allow the system to page out data to virtual memory on the physical disk. In SQL Server 2012, the memory architecture has been simplified for the usage of locked pages across all editions including processor. In SQL 2012 we enable this option by allowing the account which runs the SQL Server service to have this right. The below illustration shows the requirements for different editions of SQL server for Locking pages in memory.If you have migrated your SQL server to SQL 2012 from SQL 2005 or SQL 2008 and if you have been using this option then you can remove the trace flag 845 from the start up parameters.

We will now see how to enable this option.

1. Click Start, click on Run. Type gpedit.msc. Click on OK and you will find the Local group policy editor opening.
           
2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
   
3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
   
4. Once you open the User Rights Assignment folder all the policies will be displayed. Locate “Lock pages in Memory”. Double Click on “Lock pages in memory”
   
5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
   
   We can verify whether SQL Server 2012 is using “locked pages” by the following ways:
   1) Run the following query:

select od.node_id, os.memory_node_id, od.node_state_desc, os.locked_page_allocations_kb
from sys.dm_os_memory_nodes os
inner join sys.dm_os_nodes od on
os.memory_node_id = od.memory_node_id
where od.node_state_desc <> 'ONLINE DAC'

The locked_page_allocations_Kb will display a non-zero value.

              2) DBCC MEMORYSTATUS will show the following:
I hope this article was helpful in understanding how we can enable this option for SQL Server 2012.