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. :-)