Lock Pages in Memory

Question

Lock Pages in Memory

sqlbee19

SSCommitted

Points: 1597
More actions
October 21, 2010 at 8:42 am

#157274

Hi All,
My Server Specs are as follows -
OS - Microsoft Windows server 2008 R2 Enterprise -X64
Ram -128 GB
SQL Version : 9.00.4035.00SP3Enterprise Edition (64-bit)
I am running into this following issue on the above server.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 57644 seconds. Working set (KB): 134132, committed (KB): 738604, memory utilization: 18%.
Questions -
1) Do I need to allow Lock Pages in memory for the SQl server account(Service Account) to aviod this error ? but BOL says it is not required for 64 based PC's
2) Do I need to install Cunilative Update - 4 (Hot fix) on sql server 2005 to avoid above error ?
Did anyone installed Update -4 Hot fix ?
Any help is really appreciated ?

Viewing 9 posts - 1 through 8 (of 8 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

sqlbee19 (10/21/2010)
1) Do I need to allow Lock Pages in memory for the SQl server account(Service Account) to aviod this error ? but BOL says it is not required for 64 based PC's

It's the easiest way to solve the issue.

Another way is installing a (big) set of hotfixes that correct windows' memory trimming algorithm, that was quite aggressive in Win2003.

To be honest, I have no idea if this applies to win2008 as well, but it's worth taking a look:

http://support.microsoft.com/kb/918483/en-us/

2) Do I need to install Cunilative Update - 4 (Hot fix) on sql server 2005 to avoid above error ?

No. It's an OS issue, the cumulative update doesn't address OS issues.

Did anyone installed Update -4 Hot fix ?

Not yet.

-- Gianluca Sartori

sqlbee19 SSCommitted Points: 1597 More actions · Answer 2

Thanks.

Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

JeremyE SSCoach Points: 16197 More actions · Answer 3

sqlbee19 (10/21/2010)
Thanks.
Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

You should definitely set max memory so SQL doesn't starve out the OS. I would start with setting max memory somewhere in the 104-112GB range if the server with 128GB of memory is a dedicated SQL server. Less if it is not.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

from the horses mouth............

http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

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

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

did you look at the paging file...

what is your paging file size and what is your physical server?

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

sqlbee19 SSCommitted Points: 1597 More actions · Answer 6

The paging size I allocated for windows is 100 GB as we have plenty of space.

<<<<what is your physical server? >>>>

I didnt get this question

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

sqlbee19 (10/21/2010)
Thanks.
Do I need to set Max memory and min memory if I enable lock pages in memory setting ???

The KB link posted above by Gianluca Sartori quotes the following

Microsoft KB918483
Windows Server 2008 improves the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. If you are running SQL Server on a Windows Server 2008-based computer, you do not have to manually configure the max server memory property as specified in the "Important considerations before you assign the 'Lock pages in memory' user right for an instance of a 64-bit edition of SQL Server " section.

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

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

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

October 22, 2010 at 3:13 am

#1240776

Did you check the article George suggested you?

My blog