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
Aim:
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.
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 “
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.
Assessment:
The main criteria of success
is that the user experience has improved.
For further
reference on these changes goto http://support.microsoft.com/kb/918483
