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

avatar image

414 25 26 31

(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

more ▼

answered Mar 18, 2011 at 02:36 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

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

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: 774 times

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

Copyright 2018 Redgate Software. Privacy Policy