Ordering new server to run SQL Server databases and data
warehouse with Analysis Services. What is the max memory
each instance of SQL can support in the 32 bit world? The
four gig limit appears to be per query, but I have been
told it is per instance.

That depends on the edition of the OS and the edition of sql server.
Datacenter and Enterprise Editions can support up to 64GB. See maximum
capacity specifications in BooksOnLine for the whole chart.



--

Andrew J. Kelly
SQL Server MVP


"Rhonda Neel" <dbases (AT) aol (DOT) com> wrote

I've seen it along with a pound of other documentation,
but I can't quite put it all together. Will SQL itself
use more than 4gb RAM per instance or does the OS use it
via AWE by making virtual memory out of actual physical
memory instead of disk space or what? What about putting
tempdb in RAM? Is this in addition to the 4GB
limitation? We are looking at Win2003 Server for the OS
and SQL Enterprise Edition for the S/W.

First off tempdb in ram is no longer an option for SQL 7.0 or above. You
can set up some kind of ramdisk but it does not support it natively. As for
the ram the limitation is sort of 2 fold. First the OS can not see more
than 4GB without the PAE extensions and SQL Server can not use above 4GB
without AWE. Yes it is sort of like your virtual memory description but it
is much faster than swapping to disk. So basically each instance can use up
to 64 GB but not at the same time. I can't seem to find the memory
capacities for 2003 so I don't know what the Server edition will allow but
if it is like the Win 2000 Server it can only go to 4GB. I will have to ask
where that info is because I sure as heck can't find it<g>. If your
looking to use large amounts of ram and especially for DW applications you
might consider the 64 bit versions. That will allow up to 512GB of directly
accessed memory and is perfect for that type application.

--

Andrew J. Kelly
SQL Server MVP


"Rhonda Neel" <dbases (AT) aol (DOT) com> wrote

We considered 64 bit, but wouldn't get the performance
without using Itanium processors and we couldn't afford
the box. I have all the data about AWE and PAE, but I
still am unclear on how you can use more than 4GB ram,
which is what IT is telling us we cannot do. If we have
Win2003 and SQL2K Enterprise, and we use AWE and PAE, how
do we use more than 4GB per instance exactly?

Rhonda,

You keep saying that you have Win2003 but not what edition you have or how
much ram you have in total. The edition will dictate how much ram the OS
can use and SQL can't use more than the OS. Assuming your edition allows
say 8GB then you can set the /3gb switch on in the boot.ini file. This
allows SQL Server (or any application) to use up to 3GB or the 4GB of
directly addressable ram, leaving 1GB for the OS. Then by adding the /PAE
switch it will fool the OS into thinking it can address 36bit addressing vs
just 32. Now you turn on the AWE switch in SQL Server and that allows it to
take advantage of the memory above 4GB and make it look like it is native
memory. The AWE extensions will do the work of remapping the segments of
memory above 4GB into the address window of the memory below 4GB. So just
by setting those switches you can use more than 4GB of ram assuming you have
it of coarse and the OS is willing.

--

Andrew J. Kelly
SQL Server MVP


"Rhonda Neel" <dbases (AT) aol (DOT) com> wrote

Just a curiosity, where do the AWE extension is set?

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Directly from BooksOnLine under "awe enabled option":

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO


--

Andrew J. Kelly
SQL Server MVP


"WhiteJul" <whitejul (AT) hotmail (DOT) com> wrote

We requested Win2003 Enterprise and SQL2K Standard, but
will have to change that to Enterprise if we get the
memory we asked for. Your summarization of everything I
have heard and read was perfect and just what we needed
for purchase justification. I am also very curious about
the ramdisk configuration to replace tempdb in ram you
mentioned. Got any info or articles on how to accomplish
that?