Re: Locking pages in memory

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: 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. ...
    (microsoft.public.sqlserver.setup)
  • 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
    ... 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)