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. LP1If 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.
          LP2         LP3
        2. On the Local Group Policy Editor, expand Computer Configuration -> expand windows Settings -> Security settings
          LP4 LP5
        3. Open Security Settings, and then Open Local Policies and then open “User Rights Assignment” folder
          LP6
        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”
          LP7LP8
        5. Click on “Add user or Group” and add the account that runs SQL Server service. Restart SQL server.
          LP9
          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.

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

Advertisements

Tagged: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: