December 6, 2011 at 3:10 pm
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. 🙂
December 6, 2011 at 3:16 pm
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.
December 6, 2011 at 3:23 pm
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! 🙂
December 6, 2011 at 3:31 pm
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.
December 6, 2011 at 3:39 pm
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! 🙂
December 6, 2011 at 3:40 pm
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
December 6, 2011 at 3:46 pm
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.
December 7, 2011 at 6:36 am
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.
December 7, 2011 at 7:43 am
the page lock in sql server 2008 x64 is configure by AWE,but you can't close it.
December 7, 2011 at 7:49 am
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
December 7, 2011 at 7:59 am
December 7, 2011 at 8:02 am
the detail for
AWE,lock page in 64bit and 32 bit
December 7, 2011 at 8:12 am
I just went through all of this two days ago setting up a new 64 bit server. I think what fanzhouqi is referring to is that when the SQL Server service account has the lock pages in memory permission on a 64-bit system, SQL Server by default uses the AWE APIs for memory management, which results in slightly better performance than the normal memory management APIs. Checking the AWE box on the server configuration screen does nothing - it's a NOP. His second link says this, although it's not super clear.
On other thing the OP may want to consider is enabling trace flag 834. See http://support.microsoft.com/kb/920093. This flag requires Enterprise edition and the Lock Pages In Memory permission. The flag lets SQL use large memory pages instead of Windows normal 4k pages.
December 7, 2011 at 8:23 am
shaun.stuart (12/7/2011)
On other thing the OP may want to consider is enabling trace flag 834. See http://support.microsoft.com/kb/920093. This flag requires Enterprise edition and the Lock Pages In Memory permission. The flag lets SQL use large memory pages instead of Windows normal 4k pages.
I would strongly recommend not doing that. It's something that was documented because it was used in a TPC benchmark. It prevents dynamic sizing of the buffer pool and it requires that the memory for the buffer pool be physically contiguous, if it's not then the amount of memory allocated could be much lower than max server memory. It can also significantly slow down SQL's startup
http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx
Large page for the buffer pool is definitely not for everyone. You should only do this for a machine dedicated to SQL Server (and I mean dedicated) and only with careful consideration of settings like ‘‘max server memory’. Furthermore, you should test out the usage of this functionality to see if you get any measureable performance gains before using it in production.
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
December 8, 2011 at 1:11 pm
In our experience on a Hyper-v Guest running Win Server 08 64 bit the cpu usage grew slowly/steadily over time from page swapping until a reboot. As we added some monitoring software and got busier it grew much quicker. Our baseline usage is fairly low. The baseline would get up to 70%, we would reboot and it would go back to 15%. With lock pages in memory enabled the growth slowed substantially and with a tweek or 2 to better accomodate the monitoring usage, baseline cpu usage stays between 15 and 30%. We have 5 instances of SQL running on the guest with 70-80 (250GB tot) mostly low usage databases. This guest runs on about 30gb of memory mostly allocated to the SQL instances.
FisherDad
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply