sqlserver memory

What is the use of Lock pages in Memory setting?
> What happens if we enable Lock Pages in Memory setting
more ▼

asked Mar 18, 2011 at 02:29 AM in Default

Manikreddy gravatar image

414 24 26 28

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Lock Pages in Memory is a Windows permission to allow the named user to keep its data space in RAM rather than being paged out, to improve performance.

However, if you enable this for the SQL Server service user, you should also put a hard limit on the amount of memory that SQL Server should allocate to itself, to make sure that Windows processes can still get a look-in!

You should team this with use of AWE (Address Windowing Extensions) (on 32-bit servers).

[See documentation for SQL2008][1]

[1]: http://msdn.microsoft.com/en-us/library/ms190730.aspx
more ▼

answered Mar 18, 2011 at 02:36 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

Thanks Thomas, why lock pages in memory is no need in 64 bit one?
Mar 18, 2011 at 05:06 AM Manikreddy

Lock pages can definitely be needed in 64 Bit environments, the part which is not needed for 64 Bit is AWE as 64 Bit systems can address the ememory without that feature being enabled. AWE has no effect when turned on in a 64 Bit system anyway.

If you have a 64 Bit system with shedloads of RAM and you are running multiple instances of SQL Server (or lots of different things as well as SQL Server) and don't want each instance stealing memory from the other, you would give each user that is running the instance (I assume they are different) the right to lock memory. You then set each instance to take their chunk of the available RAM and lock that so the other instance or another process can't go and take some RAM back.
Mar 18, 2011 at 05:22 AM WilliamD
Lock Pages in Memory was necessary if you wanted to use AWE (as otherwise, performance would suffer). 64-bit servers don't have AWE because they don't need it (the addressable amount of memory on a 64-bit server is significantly larger than a 32-bit server). It can still be very useful in a 64-bit environment, though, by giving SQL Server more control over what pages get swapped out. Here is some more information on the topic.
Mar 18, 2011 at 05:28 AM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left

This article and the KB articles should help:

SQL Server and the “Lock pages in memory” Right in Windows Server

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

[How to determine the appropriate page file size for 64-bit versions of Windows][3]

[3]: http://support.microsoft.com/kb/889654/
more ▼

answered Mar 22, 2011 at 04:09 AM

DaniSQL gravatar image

4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 18, 2011 at 02:29 AM

Seen: 627 times

Last Updated: Mar 18, 2011 at 02:29 AM