共用方式為

X64, Lock Pages in Memory and AWE - Should I or Should I Not?

  • 發行項

I've heard this question thousands of times so I've thought to try to summarize some recommendation in this blog post. 

 

For 32 bits, things are pretty straight-forward.

If they are not, I've posted some comments some time ago:

\3GB \PAE and AWE - Taking away (some) confusion

https://blogs.technet.com/beatrice/archive/2008/08/29/3gb-pae-and-awe-taking-away-some-confusion.aspx

 

For 64 bits, things are a bit more complicated.

 

First of all -simple things first -  you don't need to enable AWE on a x64 machine, as the option is actually ignored.

 

What you can do, is to enable the Lock Pages in Memory Option.

This in the policy which determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk.

 

So - should you enable this option?

If you look on BOL (Books on Line) Microsoft recommendation seem a bit controversial:

- 2005 BOL: https://technet.microsoft.com/en-us/library/ms190730(SQL.90).aspx

"Although not required, we recommend locking pages in memory when using 64-bit operating systems"

- 2008 BOL: https://technet.microsoft.com/en-us/library/ms190730.aspx

"Locking pages in memory is not required on 64-bit operating systems"

 

What's the solution then?

Well, as in most cases, there are not simple and easy solutions.

You can hit serious issues with Lock Pages in Memory Option enabled as it can cause freezing, timeouts and application failures. Especially if your memory (Max and Min) is not sized properly.

 

You can therefore wait to see paging messages logged on your system, as for example

"A significant part of sql server process memory has been paged out. This may result in a performance degradation" 

and then opt to turn on Lock Pages in Memory Option,

 

Or

 

You can set it up from the beginning, but make sure to size properly max and min memory setting by capturing a Performance Monitor log to determine the memory requirements of various applications and services that are installed on the system.

 

More information:

 

Do I have to assign the Lock Pages in Memory privilege for Local System?

https://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx

 

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005

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

 

- Beatrice Nicolini -

Comments