Lock Pages in Memory

Lock Pages in Memory

It is a standard configuration setting for SQL Server.  Some times Server forced to page out large quantities of memory because of memory pressure  from windows then immediately for some time SQL Server performance will decreased. By enabling Lock Pages in Memory  SQL Server ignores the request from windows to release memroy(RAM sucn as data cache). This problem occurs much less in SQL Server instances running under SQL Server 2008.

Every time SQL Server try to fetch data pages from memory instead of fetching for Disk to reduce the disk I/O.  With this behaviour SQL Server performance will increase.

The Lock Pages in Memory option is set to OFF by default in SQL Server. If you have system administrator permissions, you can enable the option manually by using the Windows Group Policy tool (gpedit.msc) and assign this permission to the account that SQL Server is running.

Below are the steps to enable Lock Pages in Memory.

è  Click on start button click on Run. In open box type   gpedit.msc and lick on OK button. It will open Local Group Policy Editor Console Window.

 è  On the Local Group Policy Editor console, expand Computer Configuration, then expand Windows Settings, then  expand Security Settings, then expand Local Policies. In Local Policies Select User Rights Assignments folder. In the right side pane it will display all policies information. In the right side pane you can find the Lock pages in memory option. Double click on it

è  

è  It displays below screen, click on Add User or Group and in = Select USERS, SERVICE ACCOUNTS or GROUPS  dialog box add account of SQL Server service Run as account which is administrator permission

è