Re: Locking pages in memory

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
    ... So I think the 64-bit Standard does not support locked pages in memory. ... Rick Byham, SQL Server Books Online ...
    (microsoft.public.sqlserver.setup)
  • 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: Worker Threads
    ... In order to use more than 4GB of memory you must use AWE. ... One is you must have Enterprise Edition of SQL ... set in the Boot.ini and AWE enabled in SQL Server. ...
    (microsoft.public.sqlserver.setup)
  • Re: AWE settings.
    ... dynamic AWE to work using SQL 2005, but don't quote me on that. ... Microsoft SQL Server MVP ... immediately allocates memory at system startup. ...
    (microsoft.public.sqlserver.setup)
  • Re: Inexplicable slowdowns
    ... I profiled the SQL operation and it is taking no time at all. ... I also noticed that the server was operating at the end of available physical memory and as a result of perfmon monitoring, I saw that these slowdowns occur when the available physical memory dips to 100mb or so. ... I think SQL server is the culprit. ... It's set to consume a max of 3GB. ...
    (microsoft.public.dotnet.languages.csharp)