Max memory usage for SQL Server 2K on Windows 2K3

What is the max memory (RAM) SQL Server 2000 SP3 can use
when installed on a Windows 2K3 Enterprise Edition with 16
Gb of memory?
On the properties of the server it only displays 3,5 Gb!!
Thanks
CaioStandard edition can use 2GB. Enterprise Edition can use up to 64GB, depending on the limitations of
the OS. For SQL Server to use more than 2GB (or 3GB, depending on whether boot.ini has the /3GB
switch), then you need to PAE and AWE memory addressing.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Caio" <anonymous@.discussions.microsoft.com> wrote in message
news:030601c398ea$18301780$a601280a@.phx.gbl...
> What is the max memory (RAM) SQL Server 2000 SP3 can use
> when installed on a Windows 2K3 Enterprise Edition with 16
> Gb of memory?
> On the properties of the server it only displays 3,5 Gb!!
> Thanks
> Caio|||Hi Caio,
If you want to use large memory configurations with SQL(>3gb) you will need
to use /PAE in the boot.ini file, and enable AWE within SQL, then set a Max
Server Memory to the memory that you want to allocate to SQL, but there are
some things to note
1) You could also use /3GB to limit the kernel space to 1GB and give you an
extra 1GB, but this only works up to 16GB, your server is right on that
limit and running with these settings can cause problems as you aproach
this limit. So I would let the kernel have its 2GB
2) AWE is not dynamic so SQL will take that memory and it won't give it
back, if AWE is not used SQL will release memory as the OS comes under
pressure. So if you have other applications running on the server you may
want to set the max server memory a little lower.
3) If this server is clustered how many instances of SQL are running ?
Hope this helps
Regards
Clive Challinor [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.|||I am confused: why do I need the PAE switch on if Windows
2003 Enterprise Edition by default recognize 32GB of RAM?
Shouldn't SQL Server Enterprise Edition automatically
recognize all available memory?
Thanks
Caio
>--Original Message--
>Standard edition can use 2GB. Enterprise Edition can use
up to 64GB, depending on the limitations of
>the OS. For SQL Server to use more than 2GB (or 3GB,
depending on whether boot.ini has the /3GB
>switch), then you need to PAE and AWE memory addressing.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Caio" <anonymous@.discussions.microsoft.com> wrote in
message
>news:030601c398ea$18301780$a601280a@.phx.gbl...
>> What is the max memory (RAM) SQL Server 2000 SP3 can use
>> when installed on a Windows 2K3 Enterprise Edition with
16
>> Gb of memory?
>> On the properties of the server it only displays 3,5
Gb!!
>> Thanks
>> Caio
>
>.
>|||Hi Clive, thanks for the reply.
To answer your question: this server is not clustered so
we only have one instance of SQL Server.
Why do I need the PAE switch if Win 2003 Enterprise
Edition by default recognizes up to 32 GB of memory?
Shouldn't SQL SErver automatically see that the operating
system can provide more memory than the 2 or 3 GB?
Thanks again
Caio
>--Original Message--
>Hi Caio,
>If you want to use large memory configurations with SQL
(>3gb) you will need
>to use /PAE in the boot.ini file, and enable AWE within
SQL, then set a Max
>Server Memory to the memory that you want to allocate to
SQL, but there are
>some things to note
>1) You could also use /3GB to limit the kernel space to
1GB and give you an
>extra 1GB, but this only works up to 16GB, your server is
right on that
>limit and running with these settings can cause problems
as you aproach
>this limit. So I would let the kernel have its 2GB
>2) AWE is not dynamic so SQL will take that memory and it
won't give it
>back, if AWE is not used SQL will release memory as the
OS comes under
>pressure. So if you have other applications running on
the server you may
>want to set the max server memory a little lower.
>3) If this server is clustered how many instances of SQL
are running ?
>Hope this helps
>Regards
>Clive Challinor [MSFT]
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>|||See my other reply.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:0b3b01c3997c$7f1d58d0$a601280a@.phx.gbl...
> Hi Clive, thanks for the reply.
> To answer your question: this server is not clustered so
> we only have one instance of SQL Server.
> Why do I need the PAE switch if Win 2003 Enterprise
> Edition by default recognizes up to 32 GB of memory?
> Shouldn't SQL SErver automatically see that the operating
> system can provide more memory than the 2 or 3 GB?
> Thanks again
> Caio
> >--Original Message--
> >Hi Caio,
> >If you want to use large memory configurations with SQL
> (>3gb) you will need
> >to use /PAE in the boot.ini file, and enable AWE within
> SQL, then set a Max
> >Server Memory to the memory that you want to allocate to
> SQL, but there are
> >some things to note
> >
> >1) You could also use /3GB to limit the kernel space to
> 1GB and give you an
> >extra 1GB, but this only works up to 16GB, your server is
> right on that
> >limit and running with these settings can cause problems
> as you aproach
> >this limit. So I would let the kernel have its 2GB
> >
> >2) AWE is not dynamic so SQL will take that memory and it
> won't give it
> >back, if AWE is not used SQL will release memory as the
> OS comes under
> >pressure. So if you have other applications running on
> the server you may
> >want to set the max server memory a little lower.
> >
> >3) If this server is clustered how many instances of SQL
> are running ?
> >
> >Hope this helps
> >
> >Regards
> >Clive Challinor [MSFT]
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> >.
> >|||> I am confused: why do I need the PAE switch on if Windows
> 2003 Enterprise Edition by default recognize 32GB of RAM?
> Shouldn't SQL Server Enterprise Edition automatically
> recognize all available memory?
If you have 64 bit SQL Server, yes. Remember that "ordinary" SQL Server is a 32 bit app, and the
address space for a 32 bit app is 4GB. Of these 4GB either 2 or 3 GB are reserved for the system
(depends on the 3GB switch in boot.ini). So, to get to more then 2 or 3 GB, you need the windowing
technique that PAE gives you.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Caio" <anonymous@.discussions.microsoft.com> wrote in message
news:0be801c39978$694dd6a0$a401280a@.phx.gbl...
> I am confused: why do I need the PAE switch on if Windows
> 2003 Enterprise Edition by default recognize 32GB of RAM?
> Shouldn't SQL Server Enterprise Edition automatically
> recognize all available memory?
> Thanks
> Caio
> >--Original Message--
> >Standard edition can use 2GB. Enterprise Edition can use
> up to 64GB, depending on the limitations of
> >the OS. For SQL Server to use more than 2GB (or 3GB,
> depending on whether boot.ini has the /3GB
> >switch), then you need to PAE and AWE memory addressing.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Caio" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:030601c398ea$18301780$a601280a@.phx.gbl...
> >> What is the max memory (RAM) SQL Server 2000 SP3 can use
> >> when installed on a Windows 2K3 Enterprise Edition with
> 16
> >> Gb of memory?
> >>
> >> On the properties of the server it only displays 3,5
> Gb!!
> >>
> >> Thanks
> >>
> >> Caio
> >
> >
> >.
> >

materialized view vs. denormalized table

sql2k sp3
Whats the difference between these two? Isn't a
materialized view nothing more than a denormalized table?
Dont you need to update/ repopulate them both if data in
the underlying table changes?
TIA, ChrisRChrisR,
A materialized view (i.e. Indexed View) is a denormalized table that the
system keeps up-to-date for you. So, no you would not have to repopulate
it.
Having said that, there are several rules that you have to follow to get an
Indexed View created, so you should carefully read the restrictions in the
Books Online.
Russell Fields
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR|||A denormalized table is simply a table that doesn't follow the 3nf
standards. This happens a lot to improve efficiency of the queries at the
cost of storage space.
So a denormalized table may have duplicate columns, data or other items in
it that violate the normal forms.
A materialized view however is simply a view that has been created on a
table(s) that has then had a clustered index created for that view. SQL
Server stores the clustered index on the view. This can make queries much
faster. This is especially true in instances where there is a large amount
of calculations or aggregations going on in the query itself.
You do not have to repopulate the clustered index as data is modified in the
base tables. This happens automagically. =)
There is more to learn about this topic. Check out materialized views in
the Books Online.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR|||The cost of SQL Serve maintaining an indexed view is fairly steep, compared
to you doing the work yourself. However if you denormalize you may have to
change apps as well, which would not be required for an indexed view... Also
the optimizer will automatically choose the indexed view ONLY if you are
running the Enterprise edition. In the standard edition you may create an
indexed view, but it will only be used when someone references the view
name...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR