Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Lock Pages in Memory setting for 64-bit systems Expand / Collapse
Author
Message
Posted Tuesday, December 6, 2011 3:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:48 AM
Points: 218, Visits: 784
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.
Post #1217453
Posted Tuesday, December 6, 2011 3:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:31 PM
Points: 1,431, Visits: 3,226
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.
Post #1217461
Posted Tuesday, December 6, 2011 3:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:48 AM
Points: 218, Visits: 784
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!
Post #1217465
Posted Tuesday, December 6, 2011 3:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:31 PM
Points: 1,431, Visits: 3,226
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.
Post #1217469
Posted Tuesday, December 6, 2011 3:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:48 AM
Points: 218, Visits: 784
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!
Post #1217475
Posted Tuesday, December 6, 2011 3:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 42,756, Visits: 35,844
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 2008, MVP
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

Post #1217476
Posted Tuesday, December 6, 2011 3:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:48 AM
Points: 218, Visits: 784
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.
Post #1217480
Posted Wednesday, December 7, 2011 6:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 7, 2014 1:31 PM
Points: 1,431, Visits: 3,226
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.
Post #1217850
Posted Wednesday, December 7, 2011 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 2:36 AM
Points: 9, Visits: 136
the page lock in sql server 2008 x64 is configure by AWE,but you can't close it.
Post #1217908
Posted Wednesday, December 7, 2011 7:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 42,756, Visits: 35,844
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 2008, MVP
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

Post #1217918
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse