Re: Locking pages in memory

I have confirmed that SQL Server Standard 64-bit does not use locked pages in memory, even if available in the OS.
There are some complex reasons behind this, but basically, if you are running on 64-bit Windows, you probably don't need to lock pages in memory. I think the assumption is that you are in a memory rich environment. Locked pages in memory is available in Enterprise for some specific cases.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"SMFX" <SMFX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C0CDDDB5-21C0-44FD-BE59-79408B24C063@xxxxxxxxxxxxxxxx
Thanks for looking up the clarification! It does seem odd that it was
apparently purposely removed, but I guess that may explain that they did. Be
kinda curious as to know why.

In any event, the confirmation would be helpful! Thanks for tracking it down!

Thanks,
-SMFX

"Rick Byham, (MSFT)" wrote:

I've just checked the history of the Memory Architecture topic. The 3rd
column of the 3rd row used to say that ""Lock pages in Memory" is available
for
all editions of 64 Bit version of SQL Server."
In 9/18/06 a developer filed a bug to change the text to the current text.
So I think the 64-bit Standard does not support locked pages in memory. I'm
checking with a developer though to make sure.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"SMFX" <SMFX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8D875972-C3A7-4877-AC13-9C01481F01DE@xxxxxxxxxxxxxxxx
> Rick,
>
> Thanks for the quick reply!
>
> The first article is a simple "How To" for granting an account > (explicitly
> the account SQL runs under) the permission to lock pages in memory. > The
> second article is from the SQL Server 2005 Books online titled "Memory
> Architecture" and the final table row is labeled "Locked pages in > memory
> operating system (OS) privilege (Allows locking physical memory,
> preventing
> OS paging of the locked memory.)". Under the 32-bit column, they list
> "SQL
> Server Standard, Enterprise, and Developer editions: Required for SQL
> Server
> process to use AWE mechanism." However, under 64-bit column, they list
> "SQL
> Server Enterprise and Developer editions: Recommended, to avoid > operating
> system paging."
>
> They make it sound like if you installed Windows 64-bit and put SQL > Server
> Standard on top of it, give the SQL account the right to lock pages in
> memory, that edition of SQL would not attempt to lock pages in memory.
>
> Again, this doesn't seem to make sense that the code for SQL to request > a
> lock on its pages in memory would be there in 32-bit Standard, removed > for
> 64-bit Standard, but left in for 64-bit Enterprise; seems like it would > be
> more trouble than its worth.
>
> I was just looking for confirmation one way or the other. Otherwise, > I'll
> just wait for the new hardware and try to simulate it.
>
> Thanks!
>
> "Rick Byham, (MSFT)" wrote:
>
>> I can't see the text you are talking about in your references. But >> some
>> of
>> the info you cite is not quite right.
>> Lock Pages in Memory is not a SQL Server option. It's a Windows >> option.
>> It is the AWE option that is a SQL Server setting, and you don't need >> AWE
>> when running 64 bit SQL Server.
>> -- >> Rick Byham (MSFT), SQL Server Books Online
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "SMFX" <SMFX@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:66C8E23A-B67E-4F9D-9A77-A593BDF734E5@xxxxxxxxxxxxxxxx
>> > Got a quick question about locking pages in memory and possible
>> > configurations for a new server we have coming in.
>> >
>> > We are setting up a small, general purpose database server as our >> > old
>> > one
>> > is
>> > failing hardware and its done a good 8 years of service; just not >> > worth
>> > keeping it up. With prices today, its actually cheaper for us to >> > buy a
>> > system with 8GB RAM than 4GB RAM (long story). Naturally to take
>> > advantage
>> > of it, we'd like to use 64-bit on the base OS rather than 32-bit >> > with
>> > the
>> > AWE
>> > overhead.
>> >
>> > As the databases grow, one option that could become useful is the >> > "lock
>> > pages in memory". One in particular stores descent amount of blob >> > data
>> > in
>> > the system that could see a fair improvement if data were not paged >> > to
>> > disk.
>> > This seems farily simple with:
>> > __How to: Enable the Lock Pages in Memory Option (Windows)
>> > __http://msdn.microsoft.com/en-us/library/ms190730.aspx
>> >
>> > However, looking at the article:
>> > __Memory Architecture
>> > __http://msdn.microsoft.com/en-us/library/ms187499.aspx
>> > Locking pages in memory is availabe on SQL Standard *32-bit* but not
>> > *64-bit*. If we went with a 64-bit implementation, we'd have to go >> > to
>> > SQL
>> > Enterprise to gain this feature (which is, of course, a significant
>> > cost
>> > increase).
>> >
>> > Unfortunately, sometimes various documentation is misleading, so I >> > just
>> > wanted to see what the expert verdict was with these two questions:
>> > __1. Is Locked pages in memory useful in 32-bit SQL Standard (if it >> > is
>> > even
>> > available)?
>> > __2. Are there any plans for SQL Server 2008 Standard going to have
>> > Lock
>> > pages in Memory available in the 64-bit edition?
>> >
>> > Obviously, this could be what Microsoft considers an Enterprise >> > class
>> > feature and is therefore the insentive to move up to the higher >> > system,
>> > however it seems odd they would remove the coding from 64-bit >> > Standard
>> > if
>> > it
>> > were in 32-bit Standard if it were truely demend "Enterprise" class >> > of
>> > service.
>> >
>> > Your input is greatly appreciated!
>> > -SMFX
>>


.

Relevant Pages

  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Benchmark for different edition SQL 2000
    ... Please read the links I posted about memory allocation algorithms in SQL Server. ... My concern is how to prevent the SQL server that increase the memory usage continuously? ... Enterprise edition has some distinct features that SE doesn't. ... the memory arrangement is the same between standard and enterprise? ...
    (microsoft.public.sqlserver.server)
  • Re: Locking pages in memory
    ... I've just checked the history of the Memory Architecture topic. ... So I think the 64-bit Standard does not support locked pages in memory. ... Rick Byham, SQL Server Books Online ... the account SQL runs under) the permission to lock pages in memory. ...
    (microsoft.public.sqlserver.setup)
  • Re: Locking pages in memory
    ... Lock Pages in Memory is not a SQL Server option. ... It is the AWE option that is a SQL Server setting, and you don't need AWE when running 64 bit SQL Server. ... Is Locked pages in memory useful in 32-bit SQL Standard (if it is even ...
    (microsoft.public.sqlserver.setup)