SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lock Pages in Memory setting for 64-bit systems


Lock Pages in Memory setting for 64-bit systems

Author
Message
David Moutray
David Moutray
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 895
OK, I was just in a very active Twitter discussion about this question, and I would like to get input from all of you.

In Microsoft SQL Server 2008 Internals by Kalen Delaney (et. al.), Chapter 1 (SQL Server 2008 Architecture and Configuration), the author makes the following comment:

On a 64-bit platform, the Windows policy option Lock Pages in Memory is available, although it is disabled by default. This policy determines which accounts can make use of a Windows feature to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. It is recommended that you enable this policy on a 64-bit system.


Glenn Berry also wrote a very interesting article on Lock Pages in Memory earlier this year. You can read his analysis at http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/.

I've been doing a little research on this topic and I have found some very strong opinions that you should enable this feature (unless you have a reason NOT to do so), and I've also found some very strong opinions that you should not enable this feature unless there is a specific reason TO do so. All of these opinions are from very knowledgeable and experienced people.

So, my first question is this: What do you think about this issue, and why?

My second question is, should I enable Lock Pages in Memory for a new server that I am setting up?

This server is a dedicated SQL Server, 3 Quad-Core, hyperthreaded processors, 24 GB of RAM, Windows Server 2008 R2 (64-bit) running SQL Server 2008 R2 Enterprise Edition (64-bit). Only the database engine and Integration Services will be running on this server (together with a few very minor applications, like Robocopy).

It is a physical server (not virtual). The I/O subsystem has one local RAID 1 array for the Operating System and SQL Executable files, one local Fusion I/O SSD array for the System Paging file and the rest (for the SQL data, log and temp db files) is on a SAN outside my control.

This is a new server, and so I don't have any baseline on it. However, it will be used for a very active data warehouse system which has had some problems in the past due to heavy loads creating contention. I might also add that, although I will control SQL Server on this machine, the actual administration of the machine is pretty much outside my control.

So, having said all of that, would you enable Lock Pages in Memory for this machine (or not) and why? A related question is, what would you recommend that I set for Max Server Memory?

(Recall that SSIS is running on this server as well as the database engine, but no other SQL services.)

Thank you, very much, for sharing your thoughts with me. I appreciate your insights. :-)
sturner
sturner
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2252 Visits: 3259
I would say if it is a dedicated machine and you have pre-configured the min and max memory settings for SQL server it would be fine. On the other hand, if all that is true it should not really be necessary.

The probability of survival is inversely proportional to the angle of arrival.
David Moutray
David Moutray
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 895
What is your recommendation for Max Server Memory setting. The server has 144 GB of RAM (not 24 - my bad). It runs the database engine and SSIS, but no other SQL Services. It is a dedicated SQL Server, but has a few other minor applications on it that run from time to time.

Thank you! :-)
sturner
sturner
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2252 Visits: 3259
If it were me, I would NOT set the lock pages in memory and I'd set a max memory value that would leave enough memory for the other Apps to run (and the OS !).

Now if these other apps could grab huge amounts of memory and cause paging to occur on SQL server allocations I'd consider locking pages.But then if that were the case I'd also0 consider moving them to their own separate machines (virtual or otherwise).

The probability of survival is inversely proportional to the angle of arrival.
David Moutray
David Moutray
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 895
The applications besides the SQL Server Database Engine and SQL Server Integration Services are small & all are concerned with loading data into the server. (One example is using Robocopy to copy database backup files to this server so they can be restored, and there are a couple of other, minor, file import utilities.)

Mainly, however, it is just the SQL engine and SSIS (oh, and the SQL Agent, but that is all).

I found this article by Glenn Berry on Max Server Memory settings (http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/), but I am not sure how much to leave for SSIS to run. (SSIS can be a real memory hog.)

Any thoughts are appreciated! :-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88975 Visits: 45284
It partially depends on the OS. Windows Server 2003 was very, very prone to doing massive working set trims for just about no reason, so SQL would just get its entire working set paged out frequently, hence locked pages was near essential (you could leave 50% memory free and SQL would still get tossed into the page file frequently)

Windows Server 2008 and 2008 R2 don't do that as much. They still can, but they're a lot less likely to page SQL out for no reason.

If that server is dedicated to SQL (nothing else running on it), I'd start at 130GB to SQL and see how that goes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


David Moutray
David Moutray
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 Visits: 895
Operating system is Windows Server 2008 R2 Enterprise (SP 1) 64-bit

The RDBMS is SQL Server 2008 R2 Enterprise (also 64-bit)

The only SQL Services running on it are the database engine, Integration Services (which is a big memory hog, and SQL Agent. No other application of any consequence is running on the server.
sturner
sturner
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2252 Visits: 3259
I second Gail's advice, go for 130GB allocation to SQL server and see how it works out.

The probability of survival is inversely proportional to the angle of arrival.
fanzhouqi
fanzhouqi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 140
the page lock in sql server 2008 x64 is configure by AWE,but you can't close it.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88975 Visits: 45284
fanzhouqi (12/7/2011)
the page lock in sql server 2008 x64 is configure by AWE,but you can't close it.


Huh?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search