Lock pages in memory

Question

Post reply

Lock pages in memory

madhu-686862

SSChampion

Points: 10707
More actions
December 18, 2008 at 12:41 pm

#216726

Hi,
We are getting an alram in spot light saying that free space in vertual memory is less. Could plz suggest me If we add the Admin group in Lock pages in memory, it solves this virtual memory issue.
What exactly happens when we add admin group in Lock pages in memory as below
Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy dialog box appears.
Expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Click User Rights Assignment, and then double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add User or Group.
In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
Close the Group Policy dialog box.
Restart the SQL Server service.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

if you're not sure what it does why use it. What version is your OS and SQL instance?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

madhu-686862 SSChampion Points: 10707 More actions · Answer 2

I would like to know what it does..OS windows 2003 EE R2 x64 with SP2 and SQL Server 2005 EE X64

Thank You

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

well, depending on where you read. MSDN states that AWE is only available for 32bit Ent and Dev sql2005. Technet states AWE available for Std, Ent and Dev. This has to make sense as SQL2005 Std can address as much RAM as the OS has, in which case AWE must be available for Std too otherwise how on earth would it address the extra RAM.

What the "Lock pages in memory" option does is too stop the OS from paging out the sqlserv process memory working set. Be sure to set min and max memory appropriately to leave enough RAM for the OS

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Richard Hall Valued Member Points: 66 More actions · Answer 4

I am having this problem too, but when I go into "Lock pages in memory" in the user rights assignment, the box shows no users and the add user or group is grayed out. I am logged into the machine as a local administrator. I'm perplexed. I am on Windows Server 2003 R2, latest patches, enterprise edition with SQL Server 2000, Enterprise edition and fully patched.

Could R2 have anything to do with it?

Any ideas?

-Captainhall

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Perry Whittle (12/18/2008)
well, depending on where you read. MSDN states that AWE is only available for 32bit Ent and Dev sql2005. Technet states AWE available for Std, Ent and Dev. This has to make sense as SQL2005 Std can address as much RAM as the OS has, in which case AWE must be available for Std too otherwise how on earth would it address the extra RAM.

In this case, the Lock Pages in memory has nothing to do with AWE. The server is x64 so it doesn't have the 4GB VAS limitation that x86 does. Lock Pages in memory is recommended for 64bit SQL Servers as is properly setting the size of the Max Server Memory. The reason for this is that a x64 server has 8TB of VAS (2^64) which allow SQL to address all of the memory provided in the server.

What is happening is, SQL uses all of the memory on the server for it's BPool, and the OS triggers Memory Pressure. This set a flag in SQL internally to trim memory, but the OS also pages the SQL process into the Swap file. When this occurs memory is freed and the OS turns off the memory pressure flag, causing SQL Server to turn off its internal flag, and since memory is now available on the server, it turns on the grow memory flag, and the process repeats. It is a cyclic problem and eventually can result in SQL paging against itself.

Locking pages in memory prevents the process space for SQL from being paged into swap space which prevents the freeing of memory, and can starve the OS. This is why it is important to set Max Memory in SQL to reserve space for the OS to operate. Generally speaking, 2GB should be reserved on servers with 8-32GB or physical memory. You can actually play with the settings and find the ideal number for your system, but you should consistently have 150MB+ of available memory on the server.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Richard Hall (12/29/2008)
I am having this problem too, but when I go into "Lock pages in memory" in the user rights assignment, the box shows no users and the add user or group is grayed out. I am logged into the machine as a local administrator. I'm perplexed. I am on Windows Server 2003 R2, latest patches, enterprise edition with SQL Server 2000, Enterprise edition and fully patched.
Could R2 have anything to do with it?
Any ideas?
-Captainhall

have you double checked your user rights on that machine

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉