3GB once again

Axel wrote on Mon, 05 Feb 2007 13:03:57 +0100:

> Hi,
> In the following configuration:
> - SQL Server 2005 Standard SP1
> - Windows 2003 Server R2 Standard
> - 4 GB physical memory
> - /3GB and /UserVA=3072 in boot.ini
> the maximum amount of memory I can configure SQL Server to use (Options
> dialog) is 2 GB.
> I found contradictory statements in the Web (see below) as well as in
> the MS SQL Server docs (limited by OS memory) for the Standard Edition.
> Is there a 2 GB limit for SQL Server 2005 Standard on Windows 2003 Server?
According to BOL you need to set the "awe configured" option to "1" using
"sp_configure", and then restart SQL Server.
sp_configure 'awe enabled', 1
RECONFIGURE
Dan
Axel wrote on Mon, 05 Feb 2007 14:27:49 +0100:

> Daniel Crichton wrote:
> Thanks for the answers so far; however, according to the BOL, there is no
> sense in using AWE for servers with memory less or equal to 4 GB. Also,
> AWE is - as far as I know - only supported from Windows 2003 Enterprise.
Apparently the BOL is wrong, and AWE is supported and required in SQL Server
2005 Standard Edition to access more than 2GB RAM - you also need the "Lock
Pages In Memory" permission set for the account SQL Server runs under.
From my limited understanding, for Windows 32-bit or any application running
on it to access memory above 2GB there are two requirements: (a) Windows has
AWE enabled (which you've done using the /3GB flag in the boot.ini file),
and (b) the application must know it's using AWE (which for SQL Server is to
set 'awe enable' and to have "Lock Pages In Memory" set for the Windows
account).
Also, from the little info I've seen on /UserVA, setting it to 3072 is
useless - the /3GB flag already sets the user mode available memory to 3GB,
which is 3072MB. /UserVA is supposed to be used to reduce the amount of RAM
allocated to user mode to make more available to kernel mode, increasing the
amount available for PTEs. With a value of 3072 nothing is being returned to
kernel mode. However I don't think this is the cause of SQL Server not
having access to the extra memory.
Dan
|||Daniel wrote to Axel Bender on Mon, 5 Feb 2007 15:18:03 -0000:

> Axel wrote on Mon, 05 Feb 2007 14:27:49 +0100:
>
> Apparently the BOL is wrong, and AWE is supported and required in SQL
> Server 2005 Standard Edition to access more than 2GB RAM - you also need
> the "Lock Pages In Memory" permission set for the account SQL Server runs
> under.
Forgot to cite the reference for "Lock Pages In Memory":
http://msdn2.microsoft.com/en-us/library/ms190730.aspx
Dan
|||By the way, when you right-click "My Computer", then "Properties", do
you see all your memory in that box? Do you see the words "Physical
Address Extension"? You should...
Axel Bender wrote:
> Daniel Crichton wrote:
> Hello Dan,
> Thanks for your help. Unfortunately (after creating a new local account
> (SQL Server Configuration Manager), verifying it is working (i.e.
> restarting the Service), and attaching the "Lock local pages" right to
> it) nothing changed (yes, I rebooted...). I'm still not able to select
> an amount of memory above 2 GBs to the SQL Server Service (options
> page). Maybe there *is* a limitation in the Standard Edition?! Btw., I'm
> using both the 32bit Windows and the 32bit SQL Server Editions.
> Axel
-Dave Markle
http://www.markleconsulting.com/blog