3GB once again

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?
thx
AxelHi,
No there is no memory Limit for SQL Standard edition.. Using SP_CONFIGURE en
able the option 'AWE Enabled' by setting the value to 1. After that set the
max server memory
value to 3 GB for your SQL Server.
Thanks
Hari
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:eq76ee$unj$00$
1@.news.t-online.com...
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 dial
og) 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?
thx
Axel|||Hari
It is correct to 32 bit version as I understood . For 64 bit version you ha
ve to grant permission to the account on Lock Page memory and there is not
reason to enable AWE.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:OQf$TaSSHHA.
4260@.TK2MSFTNGP06.phx.gbl...
Hi,
No there is no memory Limit for SQL Standard edition.. Using SP_CONFIGURE en
able the option 'AWE Enabled' by setting the value to 1. After that set the
max server memory
value to 3 GB for your SQL Server.
Thanks
Hari
"Axel Bender" <axel_bender@.t-online.de> wrote in message news:eq76ee$unj$00$
1@.news.t-online.com...
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 dial
og) 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?
thx
Axel|||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|||Daniel Crichton wrote:
> 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
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.|||Just as an addendum:
I tried it: I configured AWE for SQL Server 2005 and rebooted, to no
avail (which - this time - is in accordance with the docs).
Axel|||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|||Daniel Crichton wrote:
> Forgot to cite the reference for "Lock Pages In Memory":
> http://msdn2.microsoft.com/en-us/library/ms190730.asp
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|||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