Lock Pages in Memory for x64

I always have difficulty remembering exactly where this KB article was, what KB number, has Microsoft renumbered it and of course for many of these options you don’t carry them out every day – so this is locking pages in memory – essential for x64 Enterprise and Developer, but not possible in Standard edition for some reason.

 

This is the cut and paste from the various msdn/KB  plus some additional comments. Links at end.

 

For  x32 you must use AWE to access extended memory ( I’m not including the sole use of 3GB switch here as in certain circumstances that can cause more problems than its worth – I’d usually advise using AWE with 4GB of ram rather than the 3GB switch – yes AWE does work with 4GB ram. You cannot enable AWE without Lock Pages in Memory set.

 

For x64 you only have Lock Pages in Memory, and this only works for Enterprise and Developer, if you’re using Standard Edition there is no option.

 

x32

x64

Conventional memory

All SQL Server editions: Up to process virtual address space limit:

·         2 GB

·         3 GB with /3gb boot parameter

·         4 GB on WOW64

All SQL Server editions: Up to process virtual address space limit:

·         8 terabytes on x64 architecture

http://msdn2.microsoft.com/en-us/library/ms187499.note(en-US,SQL.90).gifNote:

On Windows Server 2003, the limitation is 512 GB; and on Windows Server 2003 Service Pack 1, the limitation is 1 terabyte. When Windows supports additional memory, SQL Server can reach the limits listed.

AWE mechanism (Allows SQL Server to go beyond the process virtual address space limit on 32-bit platform.)

SQL Server Standard, Enterprise, and Developer editions: Buffer pool is capable of accessing up to 64 GB of memory.

Note that the sp_configure  awe enabled option is present on 64-bit SQL Server, but it is ignored. It is subject to removal in future releases or service packs of 64-bit SQL Server.

Locked pages in memory operating system (OS) privilege (Allows locking physical memory, preventing OS paging of the locked memory.)

SQL Server Standard, Enterprise, and Developer editions: Required for SQL Server process to use AWE mechanism. Memory allocated through AWE mechanism cannot be paged out.

Granting this privilege without enabling AWE has no effect on the server.

SQL Server Enterprise and Developer editions: Recommended, to avoid operating system paging. Might provide a performance benefit depending on the workload. The amount of memory accessible is similar to conventional memory case.

 

·         In x32 world you can’t enable AWE without lock pages, however in x64 it is unset, this means dynamic memory doesn’t really work and you can soon find yourself struggling with a very unresponsive SQL Server.

·         The setting of the Large System Cache can be a factor and whilst this is limited in x32 it has no such limitations in x64.

·         My personal advice would be to leave at least 4Gb of memory outside the SQL Server allocation on a X64 box, there can be seriously hefty memory allocations outside SQL Server especially on Servers with lots of cores, and make sure you turn off Hyperthreading.

 

Local Computer Policy Settings

 

1.       On the Start menu, click Run. In the Open box, type gpedit.msc.

a.       The Group Policy dialog box opens.

2.       On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.

3.       Expand Security Settings, and then expand Local Policies.

4.       Select the User Rights Assignment folder.

a.       The policies will be displayed in the details pane.

5.       In the pane, double-click Lock pages in memory.

6.       In the Local Security Policy Setting dialog box, click Add.

7.       In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

 

Image3.jpg

SQL  error log entry when locked pages enabled. ( x64 )

 

http://support.microsoft.com/kb/918483

 

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/05/03/using-64-bit-sql-server-2005-lock-pages-in-memory.aspx