Wednesday, 17 February 2010

Lock Pages In Memory

There is a lot of discussion about lock pages in memory on the internet so I'm not going to repeat that discussion. What I have found on some of my 64 bit physical servers I have found lock pages in memory to provide a massive improvement in performance from the perspective of end users.

Symptomatically, prior to using lock pages in memory, we would see the memory use rising (in Task manager) and then at a point close to 100% the server would start paging at an enormous rate (attempting thousands of pages a second) until the memory availability reduced down to a level Windows was obviously happy with. Repeat until users scream.

During the heavy paging activity the system would become unresponsive when combined with attempts at normal activity and that period lastest as long is it took our hardware to sort itself out - more memory = more time.

Turning on lock pages in memory changes that behaviour so that Windows doesn't reach a tipping point and decide to retrieve all that RAM for it's own purposes - essentially Windows really does hand over management to the extent that in Task Manager the SQLServer.exe process no longer shows how much RAM is in use on that process (which is annoying as it was easy to see where all the memory was but of course if Windows isn't managing that memory any more why would it care).

So from the user perspective locking pages in memory for SQL server prevents stop and go performance changes due to paging activity.

Configuration is simple but requires a reboot, here's a sample request to a service provider to get the change approved I raised  :

XYZ server Configuration Change

To turn on the “lock pages in memory” configuration to improve the performance of the XYZ SQL Server from the perspective of users - essentially this setting allows SQL Server to prevent the operating system from paging out buffer pool memory that SQL Server is actively using. This is the same change as carried out against AAA Server successfully last year.

Step 1:
The usual first step is to change SQL Server to set a maximum amount of memory that will be consumed – this will ensure we reserve some memory for operating system purposes. This setting is changed on the memory tab of the SQL Server properties and should allow 2 -3 Gb to remain the Operating system + any out of buffer pool RAM that SQL requires, in this instance 3Gb is sufficient.

Step 2:
Change the machine policy to enable the “lock pages in memory” settings. This change must be made to both nodes of of a cluster. Therefore, this step consists of :
1)             Identify the current passive node.
2)             Make the following change to the current passive node as follows:
a.     Click Start, click Run, type gpedit.msc, and then click OK.
Note The Group Policy dialog box appears.
b.    Expand Computer Configuration, and then expand Windows Settings.
c.     Expand Security Settings, and then expand Local Policies.
d.    Click User Rights Assignment, and then double-click Lock pages in memory.
e.     In the Local Security Policy Setting dialog box, click Add User or Group.
f.     In the Select Users or Groups dialog box, add “”, “Administrators” and “<your cluster service account if a cluster>” , and then click OK.
g.    Close the Group Policy dialog box.
3)             Restart the node.
4)             Check that the change is in place.
5)             Fail over to the node just changed.
6)             Repeat the above changes on the new passive node.
7)             Restart the node.
8)             Fail back to the usual node.

The change should be carried out by maintenance staff who should confirm that it will not be overwritten by domain wide policy application.

Step 3:
Email DBAs who can confirm that the configuration change has taken effect, this should take the form of the message “Using Locked Pages For Buffer Pool” appearing in the SQL Server logs.

The main criteria of success is that the user experience has improved.

For further reference on these changes goto