Max. memory limit for SQL Standard edition.

I read somewhere that the SQL Standard edition can only use the memory up to
2Gb.
Is it the same limit for SQL 2000 & 2005 ?
Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb instead
of 2Gb ?Johnny wrote:
> I read somewhere that the SQL Standard edition can only use the
> memory up to 2Gb.
> Is it the same limit for SQL 2000 & 2005 ?
> Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb
> instead of 2Gb ?
Indeed, SQL Server 2000 Standard Edition supports maximum 2GB of RAM.
However, SQL Server 2005 Standard Edition supports the maximum memory
supported by the Operating System.
On 32-bit systems, the user-mode virtual address space for each 32-bit
process is 2GB (or 3GB if you use the /3GB in boot.ini). However, most
32-bit OS-s support PAE and/or AWE. PAE allows the OS to access more
than 4GB of memory and AWE allows an application to allocate memory
beyond the 4GB limit.
For more information, see:
http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
http://msdn2.microsoft.com/en-US/library/ms143685.aspx
http://msdn2.microsoft.com/en-us/library/aa366778.aspx
http://msdn2.microsoft.com/en-us/library/aa366796.aspx
http://msdn2.microsoft.com/en-us/library/aa366527.aspx
Razvan Socol
SQL Server MVP|||Hi,
So the maximum memory limit for SQL2000 & SQL2005 is different.
In order to use more than 4Gb of memory in SQL2005, /PAE & AWE has to be
set. However, AWE is only available for SQL2005 Enterprise or Developer
edition. How Standard edition can use above 4Gb of memory.
From books online (dated April 2006).
"Support for AWE is available only in the Enterprise and Developer editions
and only applies to 32-bit operating systems. " (OR it is misleading ?)
Also from my experience, I did try to set AWE in SQL2005 standard edition
but it is not allowed. I cannot remember what the error message was. At the
end, I had to uninstall standard edition and reinstall enterprise edition.
Any ideas ? Thanks for your advices.
"Razvan Socol" wrote:
> Johnny wrote:
> > I read somewhere that the SQL Standard edition can only use the
> > memory up to 2Gb.
> > Is it the same limit for SQL 2000 & 2005 ?
> > Also if I set /3GB in the boot.ini, can the memory be used up to 3Gb
> > instead of 2Gb ?
> Indeed, SQL Server 2000 Standard Edition supports maximum 2GB of RAM.
> However, SQL Server 2005 Standard Edition supports the maximum memory
> supported by the Operating System.
> On 32-bit systems, the user-mode virtual address space for each 32-bit
> process is 2GB (or 3GB if you use the /3GB in boot.ini). However, most
> 32-bit OS-s support PAE and/or AWE. PAE allows the OS to access more
> than 4GB of memory and AWE allows an application to allocate memory
> beyond the 4GB limit.
> For more information, see:
> http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx
> http://msdn2.microsoft.com/en-US/library/ms143685.aspx
> http://msdn2.microsoft.com/en-us/library/aa366778.aspx
> http://msdn2.microsoft.com/en-us/library/aa366796.aspx
> http://msdn2.microsoft.com/en-us/library/aa366527.aspx
>
> --
> Razvan Socol
> SQL Server MVP
>|||Johnny wrote:
> From books online (dated April 2006).
> "Support for AWE is available only in the Enterprise and Developer
> editions and only applies to 32-bit operating systems. " (OR it is
> misleading ?)
SQL Server 2005 Books Online (May 2007) says:
"Support for AWE is available only in the SQL Server 2005 Enterprise,
Standard, and Developer editions and only applies to 32-bit operating
systems."
See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
You can download SQL Server 2005 Books Online (May 2007) from:
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
220-b133-29c1e0b6585f
However, even if you are using AWE, you are still limited by the
maximum amount of RAM accessible by the Operating System. For example,
Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
need Windows Server 2003 Enterprise Edition or Datacenter Edition.
See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
--
Razvan Socol
SQL Server MVP|||Thanks for your advices.
I know all these factors have to be considered (e.g windows Enterprise
edition).
What is the best way to find out how much memory the SQL Server is currently
using ? (from task manager to show memory usage or use performance monitor).
If using performance monitor, what are the counters needed to check ? (SQL
Server memory manager ?)
Regards,
Johnny
"Razvan Socol" wrote:
> Johnny wrote:
> > From books online (dated April 2006).
> > "Support for AWE is available only in the Enterprise and Developer
> > editions and only applies to 32-bit operating systems. " (OR it is
> > misleading ?)
> SQL Server 2005 Books Online (May 2007) says:
> "Support for AWE is available only in the SQL Server 2005 Enterprise,
> Standard, and Developer editions and only applies to 32-bit operating
> systems."
> See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
> You can download SQL Server 2005 Books Online (May 2007) from:
> http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
> 220-b133-29c1e0b6585f
> However, even if you are using AWE, you are still limited by the
> maximum amount of RAM accessible by the Operating System. For example,
> Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
> To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
> need Windows Server 2003 Enterprise Edition or Datacenter Edition.
> See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
> --
> Razvan Socol
> SQL Server MVP
>|||Johnny wrote:
> What is the best way to find out how much memory the SQL Server is
> currently using ? (from task manager to show memory usage or use
> performance monitor). If using performance monitor, what are the
> counters needed to check ? (SQL Server memory manager ?)
I'm not sure about the best way, but one way would be DBCC MEMORYSTATUS.
--
Razvan Socol
SQL Server MVP|||You can also take a look at the following page:
SQL Server 2005 - Monitoring Memory Usage
http://msdn2.microsoft.com/en-us/library/ms176018.aspx
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:D86727A8-C8DB-4713-BB0A-A146991F5662@.microsoft.com...
> Thanks for your advices.
> I know all these factors have to be considered (e.g windows Enterprise
> edition).
> What is the best way to find out how much memory the SQL Server is
> currently
> using ? (from task manager to show memory usage or use performance
> monitor).
> If using performance monitor, what are the counters needed to check ? (SQL
> Server memory manager ?)
> Regards,
> Johnny
>
> "Razvan Socol" wrote:
>> Johnny wrote:
>> > From books online (dated April 2006).
>> > "Support for AWE is available only in the Enterprise and Developer
>> > editions and only applies to 32-bit operating systems. " (OR it is
>> > misleading ?)
>> SQL Server 2005 Books Online (May 2007) says:
>> "Support for AWE is available only in the SQL Server 2005 Enterprise,
>> Standard, and Developer editions and only applies to 32-bit operating
>> systems."
>> See: http://msdn2.microsoft.com/en-us/library/ms187499.aspx
>> You can download SQL Server 2005 Books Online (May 2007) from:
>> http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4
>> 220-b133-29c1e0b6585f
>> However, even if you are using AWE, you are still limited by the
>> maximum amount of RAM accessible by the Operating System. For example,
>> Windows Server 2003 Standard Edition (32-bit) can only use 4GB of RAM.
>> To access more than 4GB of RAM on a 32-bit Windows 2003 system, you
>> need Windows Server 2003 Enterprise Edition or Datacenter Edition.
>> See (again): http://msdn2.microsoft.com/en-us/library/aa366778.aspx
>> --
>> Razvan Socol
>> SQL Server MVP