Assigning Lock pages in Memory – SQL Server

If you have worked with SQL Server for sometime, you might be well aware of how important is the Buffer Pool for SQL Server performance. If there is a memory pressure on the Server where SQL Server is running, the Windows Operating System might page some portion of the SQL Server Buffer Pool to allocate memory for the other application. When this happens, the SQL Server’s performance is badly impacted depending on how much memory was paged to disk. In order to avoid this, the service account user under which the SQL Server runs, needs to be assigned the permissions Lock Pages in Memory.  This blog will guide the steps to assign the permissions..

Before we begin, I would like to show you a screens shot of SQL Server Configuration Manager showing the service account under which SQL Servers runs. In my case the service account is Svc-Act-SQL2008R2, so we shall be assigning the permissions to this service account.

With that, lets begin our task….

Fire up Run command by going to Start -> Run or by pressing Windows button and R at the same time. Run command should be displayed as shown below. Type gpedit.msc and click OK.

Group Policy Editor is displayed as shown in the pic below …

Expand Computer Configuration, and within the list of available groupings, choose Windows Settings and expand it. From the list, now expand Security Settings. the below picture is how it looks after this step.

Then expand Local Policies, select User Rights Assignment, you should see a list of policies listed in the right side pane. Scroll down to Lock pages in memory. Pic below…

You can right click on Lock pages in memory and select Properties. You can also double click on Lock pages in memory. In order to show it visually, I chose to right click..

Properties window for Lock pages in memory is displayed as shown below..

Click Add User or Group button and you should see the below dialog box, we need to select the User(s) or a Group whom we would like to assign this permission.

If you are aware of the user account, you can directly type in the username in the text box in the above screen and click Check Names button. To explain in detail, I would like to show the alternate way also.. Click on Advanced button and the dialog box gets maximized with some additional options, click on Find Now button to display the list of users on the machine. Scroll down to the user to select the user, and click OK.

Once you are done with the user selection, the dialog box will display the complete path of user chosen. Click OK

The user(s) is listed in the properties of Lock pages in memory as displayed in below pic…

Click OK to save the permissions and the user name should be displayed in the Local Group Policy Editor as shown below.

You should restart SQL Service for this to take effect, once you have restarted the SQL Server you can see the entry in SQL Server Error Log as shown below..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Learning SQL Server Performance Tuning – Part One

I wrote a blog in January 2010, called Performance Tuning in SQL Server. It was kind of introductory non-technical blog. (If you have time I would recommend you read it, would not take much time to digest it). In this short blog we will learn various steps involved in SQL Server performance tuning.

When dealing with performance tuning SQL Server, we can broadly classify it into 2 major areas; Physical Server related and SQL Server related. I will explain what does these areas consists of in more detail shortly, but remember that both these areas needs to be addressed in order to reach the optimum performance from the SQL Server Databases.

Tuning physical server involves tuning those underlying hardware components that define the physical server on which the SQL Server Instance is running OR SQL Server needs to be installed on. Primary hardware resources that needs to be monitored and tuned are Memory, Processors, Disk Drives and Network. 

SQL Server tuning invloves those that can be configured at the Instance and database level in SQL Server and more importantly involves in T-SQL query tuning.

In the next part of this series we shall look more details with respect to both these areas.

Note: Links to other parts in the series will be included here as and when they are published.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

– Bru Medishetty