Max Memory SQL2K EE on W2K3 EE

This is what I have
Windows Server 2003 Enterprise Edition (x86)
SQL Server 2000 Enterprise Edition (x86)
What I'm trying to clarify is if I have a W2K3 EE system with 32GB of RAM
can a single instanace of SQL2K EE access the entire 32GB
or
Will the single instance only access 8GB and therefore only if I had 4
instances would SQL Server use 32GB
Thanks,
Paul
SP3 and higher will address 32 GB of RAM on W2k3EE. I wouldn't set total
SQL memory for all instances higher than 28GB on such a system. Setting
memory higher can starve necessary OS functions like AWE mapping which will
kill performance and stability
GNH
"Paul Ward" <Paul Ward@.discussions.microsoft.com> wrote in message
news:79DC5A4B-BE32-4770-861F-ACFD2D08DB00@.microsoft.com...
> This is what I have
> Windows Server 2003 Enterprise Edition (x86)
> SQL Server 2000 Enterprise Edition (x86)
>
> What I'm trying to clarify is if I have a W2K3 EE system with 32GB of RAM
> can a single instanace of SQL2K EE access the entire 32GB
> or
> Will the single instance only access 8GB and therefore only if I had 4
> instances would SQL Server use 32GB
> Thanks,
> Paul
|||Thanks Geoff.
I've looked through the release notes for SP3 and can't find any reference
to this.
Have you seen this documented somewhere or is your answer based on a system
you have worked with?
"Geoff N. Hiten" wrote:

> SP3 and higher will address 32 GB of RAM on W2k3EE. I wouldn't set total
> SQL memory for all instances higher than 28GB on such a system. Setting
> memory higher can starve necessary OS functions like AWE mapping which will
> kill performance and stability
> GNH
>
> "Paul Ward" <Paul Ward@.discussions.microsoft.com> wrote in message
> news:79DC5A4B-BE32-4770-861F-ACFD2D08DB00@.microsoft.com...
>
|||Is a Microsoft KB article good enough?
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750
Click on the scrolldown link to Windows 2000 Advanced Server or Windows 2000
Datacenter or Windows Server 2003
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
news:43B4240B-1E41-4BA3-B068-CF048598E1AD@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> I've looked through the release notes for SP3 and can't find any reference
> to this.
> Have you seen this documented somewhere or is your answer based on a
> system
> you have worked with?
> "Geoff N. Hiten" wrote:
|||I've seen this before and although it states that W2K3 EE supports 32GB using
PAE it doesn't actually make it clear that SQL2K EE can use (the majority) of
this.
Have you been involved with a system running W2K3 EE and SQL2K EE with a
large amount of RAM?
"Geoff N. Hiten" wrote:

> Is a Microsoft KB article good enough?
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750
> Click on the scrolldown link to Windows 2000 Advanced Server or Windows 2000
> Datacenter or Windows Server 2003
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
> news:43B4240B-1E41-4BA3-B068-CF048598E1AD@.microsoft.com...
>
|||Yes. I have built a system with 32GB of physical RAM , W2K3EE and SQL2K EE,
It happened to be a four-node cluster with 8 procs per node, just to make it
more complex. So much of my advice in this area is from hard-earned
direct knowledge.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
news:4B55AA08-7B48-4221-BBA3-8B0D4AFA7906@.microsoft.com...[vbcol=seagreen]
> I've seen this before and although it states that W2K3 EE supports 32GB
> using
> PAE it doesn't actually make it clear that SQL2K EE can use (the majority)
> of
> this.
> Have you been involved with a system running W2K3 EE and SQL2K EE with a
> large amount of RAM?
>
> "Geoff N. Hiten" wrote:
|||Thanks for help and sharing your knowledge on this
"Geoff N. Hiten" wrote:

> Yes. I have built a system with 32GB of physical RAM , W2K3EE and SQL2K EE,
> It happened to be a four-node cluster with 8 procs per node, just to make it
> more complex. So much of my advice in this area is from hard-earned
> direct knowledge.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
> news:4B55AA08-7B48-4221-BBA3-8B0D4AFA7906@.microsoft.com...
>
sql

Max Memory SQL2K EE on W2K3 EE

This is what I have
Windows Server 2003 Enterprise Edition (x86)
SQL Server 2000 Enterprise Edition (x86)
What I'm trying to clarify is if I have a W2K3 EE system with 32GB of RAM
can a single instanace of SQL2K EE access the entire 32GB
or
Will the single instance only access 8GB and therefore only if I had 4
instances would SQL Server use 32GB
Thanks,
PaulSP3 and higher will address 32 GB of RAM on W2k3EE. I wouldn't set total
SQL memory for all instances higher than 28GB on such a system. Setting
memory higher can starve necessary OS functions like AWE mapping which will
kill performance and stability
GNH
"Paul Ward" <Paul Ward@.discussions.microsoft.com> wrote in message
news:79DC5A4B-BE32-4770-861F-ACFD2D08DB00@.microsoft.com...
> This is what I have
> Windows Server 2003 Enterprise Edition (x86)
> SQL Server 2000 Enterprise Edition (x86)
>
> What I'm trying to clarify is if I have a W2K3 EE system with 32GB of RAM
> can a single instanace of SQL2K EE access the entire 32GB
> or
> Will the single instance only access 8GB and therefore only if I had 4
> instances would SQL Server use 32GB
> Thanks,
> Paul|||Thanks Geoff.
I've looked through the release notes for SP3 and can't find any reference
to this.
Have you seen this documented somewhere or is your answer based on a system
you have worked with?
"Geoff N. Hiten" wrote:

> SP3 and higher will address 32 GB of RAM on W2k3EE. I wouldn't set total
> SQL memory for all instances higher than 28GB on such a system. Setting
> memory higher can starve necessary OS functions like AWE mapping which wil
l
> kill performance and stability
> GNH
>
> "Paul Ward" <Paul Ward@.discussions.microsoft.com> wrote in message
> news:79DC5A4B-BE32-4770-861F-ACFD2D08DB00@.microsoft.com...
>|||Is a Microsoft KB article good enough?
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750
Click on the scrolldown link to Windows 2000 Advanced Server or Windows 2000
Datacenter or Windows Server 2003
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
news:43B4240B-1E41-4BA3-B068-CF048598E1AD@.microsoft.com...[vbcol=seagreen]
> Thanks Geoff.
> I've looked through the release notes for SP3 and can't find any reference
> to this.
> Have you seen this documented somewhere or is your answer based on a
> system
> you have worked with?
> "Geoff N. Hiten" wrote:
>|||I've seen this before and although it states that W2K3 EE supports 32GB usin
g
PAE it doesn't actually make it clear that SQL2K EE can use (the majority) o
f
this.
Have you been involved with a system running W2K3 EE and SQL2K EE with a
large amount of RAM?
"Geoff N. Hiten" wrote:

> Is a Microsoft KB article good enough?
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750
> Click on the scrolldown link to Windows 2000 Advanced Server or Windows 20
00
> Datacenter or Windows Server 2003
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
> news:43B4240B-1E41-4BA3-B068-CF048598E1AD@.microsoft.com...
>|||Yes. I have built a system with 32GB of physical RAM , W2K3EE and SQL2K EE,
It happened to be a four-node cluster with 8 procs per node, just to make it
more complex. So much of my advice in this area is from hard-earned
direct knowledge.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
news:4B55AA08-7B48-4221-BBA3-8B0D4AFA7906@.microsoft.com...[vbcol=seagreen]
> I've seen this before and although it states that W2K3 EE supports 32GB
> using
> PAE it doesn't actually make it clear that SQL2K EE can use (the majority)
> of
> this.
> Have you been involved with a system running W2K3 EE and SQL2K EE with a
> large amount of RAM?
>
> "Geoff N. Hiten" wrote:
>|||Thanks for help and sharing your knowledge on this
"Geoff N. Hiten" wrote:

> Yes. I have built a system with 32GB of physical RAM , W2K3EE and SQL2K E
E,
> It happened to be a four-node cluster with 8 procs per node, just to make
it
> more complex. So much of my advice in this area is from hard-earned
> direct knowledge.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Ward" <PaulWard@.discussions.microsoft.com> wrote in message
> news:4B55AA08-7B48-4221-BBA3-8B0D4AFA7906@.microsoft.com...
>

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, ChrisR
ChrisR,
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

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