Memory used by Cluster

Hi,
How can I know how much memory is used by SQL Server in Cluster? as I
believe on nodes I cannot see that.
thanks in advance
It should be the same as for a non-clustered instance.
Some good info is in BOL and in:
274750 How to configure memory for more than 2 GB in SQL Server
http://support.microsoft.com/?id=274750
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Cidy,
thanks a lot. I was looking more towards watching the memory usage in
performance monitor.

Memory Usage Active - Active

Dear all,
i am planing to implement a Windows 2003 Cluster with MS SQl 2000
Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
RAM per Node.
I have the need for 6 SQL instances and would like to implement a
active-active cluster. What do you think it the best way to configure
the memory for the Servers ?
I would like to run 3 instanced per Node but can i allocate 7 GB per
Node for SQL (and 1 for the Operating System) ?
What is happening then i one server fails ?
Should i plan to allocate only 3 GB per Server for SQL to make sure
that one server can handle the load for all instances if one server
fails ?
Should i use the /AWE switch only in the boot.ini to allow more than 4
GB Memory ?
or should i use the /3 GB switch as well ?
Maybe somebody can give me a hint.
Best regards,
Walter
Answers inline...
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Walter" <2oznet@.gmx.de> wrote in message
news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Dear all,
> i am planing to implement a Windows 2003 Cluster with MS SQl 2000
> Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GB
> RAM per Node.
> I have the need for 6 SQL instances and would like to implement a
> active-active cluster. What do you think it the best way to configure
> the memory for the Servers ?
Which instances need the most RAM? That is a lot of instance for only two
machines.

> I would like to run 3 instanced per Node but can i allocate 7 GB per
> Node for SQL (and 1 for the Operating System) ?
Again, what are the database/instance requirements.

> What is happening then i one server fails ?
>
You will then failover and have one heck of busy server!!! All 6 instances
will run, but I bet a lot slower.

> Should i plan to allocate only 3 GB per Server for SQL to make sure
> that one server can handle the load for all instances if one server
> fails ?
Can your applications handle that config? That is not a lot of memory for 3
copies of SQL to run under. Not a lot by any stretch.

> Should i use the /AWE switch only in the boot.ini to allow more than 4
> GB Memory ?
Does your hardware support /AWE? If so, yes.

> or should i use the /3 GB switch as well ?
Yes!

> Maybe somebody can give me a hint.
I would love to know your business factors that requir 6 instances of SQL.

> Best regards,
> Walter
|||There might be some dev/test cases where eveyrone wants to have the sysadmin
access to the SQL instance. It then may make sense to give each a separate
instance with the sysadmin rights to keep them isolated from each other. But
I agree you really need to think hard whether there is sufficient rationale
for having six SQL instances in a two-node cluster with 4-way servers.
Linchi
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:usaAppQ5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Answers inline...
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Walter" <2oznet@.gmx.de> wrote in message
> news:58bb24fd.0412172354.505a3c4a@.posting.google.c om...
> Which instances need the most RAM? That is a lot of instance for only two
> machines.
>
> Again, what are the database/instance requirements.
>
> You will then failover and have one heck of busy server!!! All 6 instances
> will run, but I bet a lot slower.
>
> Can your applications handle that config? That is not a lot of memory for
> 3 copies of SQL to run under. Not a lot by any stretch.
>
> Does your hardware support /AWE? If so, yes.
>
> Yes!
>
> I would love to know your business factors that requir 6 instances of SQL.
>
>

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?
You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>
|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Patricia" wrote:

> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory Pressure Question

SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
Active/Passive cluster with two nodes. AWE enabled.
We are experiencing memory pressure problems with errors such as
"unable to reserve contiguous memory" and "insufficient memory
available" which lead to fail over. I've researched causes and
applied corrections and/or recommendations accordingly. However,
there are questions regarding linked servers that I cannot find an
answer to. I've identified two queries in object cache using linked
server whose compiled plan exceeds 8K which overflows into the
MemToLeave and on to the linked server whereby linked server is known
to have memory leaks for plans exceeding 8K.
Question 1: Is the linked server 8K limitation for the query plan only
or does this mean the result set is also affected by the 8K
limitation?
Question 2: Will setting the packet size in the provider string
control the size of the query plan passed to linked server and if
applicable, control the size of the result set?You mention a fialover , so on the other node it workes fine?
Or do you have the same problem.
Sorry , not a direct answer to youre question.
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL :)
"Patricia" wrote:
> SQL Server 2000 SP4 w/ build 2187 running on OS Windows 2003 SP1.
> Active/Passive cluster with two nodes. AWE enabled.
> We are experiencing memory pressure problems with errors such as
> "unable to reserve contiguous memory" and "insufficient memory
> available" which lead to fail over. I've researched causes and
> applied corrections and/or recommendations accordingly. However,
> there are questions regarding linked servers that I cannot find an
> answer to. I've identified two queries in object cache using linked
> server whose compiled plan exceeds 8K which overflows into the
> MemToLeave and on to the linked server whereby linked server is known
> to have memory leaks for plans exceeding 8K.
> Question 1: Is the linked server 8K limitation for the query plan only
> or does this mean the result set is also affected by the 8K
> limitation?
> Question 2: Will setting the packet size in the provider string
> control the size of the query plan passed to linked server and if
> applicable, control the size of the result set?
>|||Both nodes are affected.

Memory limitations, 2005 Standard

Hi all,

I have recently implemented a SQL 2005 cluster using SQL 2005 Standard
on Windows 2003 Enterprise edition.

Both nodes have 4GB of RAM and according to the datasheet, SQL 2005
Standard can support the OS maximum memory amount and Win2K3 Ent
Edition can support 64GB!

However, in Enterprise Manager, if I go to the "Memory" tab of the
instance properties I can't increase the memory beyond 2147483647
(which I assume is around 2GB).

I don't have AWE enabled but am unsure as to the ramifications of this.

Any advice anyone could provide would be greatly appreciated.

Many thanks,

IanIM (ian@.ianmurphy.freeserve.co.uk) writes:
> I have recently implemented a SQL 2005 cluster using SQL 2005 Standard
> on Windows 2003 Enterprise edition.
> Both nodes have 4GB of RAM and according to the datasheet, SQL 2005
> Standard can support the OS maximum memory amount and Win2K3 Ent
> Edition can support 64GB!
> However, in Enterprise Manager, if I go to the "Memory" tab of the
> instance properties I can't increase the memory beyond 2147483647
> (which I assume is around 2GB).

No, that is a little over 2TB, as the unit of the parameter is MB
(or rather 2^20 bytes, I guess).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> IM (ian@.ianmurphy.freeserve.co.uk) writes:
> > I have recently implemented a SQL 2005 cluster using SQL 2005 Standard
> > on Windows 2003 Enterprise edition.
> > Both nodes have 4GB of RAM and according to the datasheet, SQL 2005
> > Standard can support the OS maximum memory amount and Win2K3 Ent
> > Edition can support 64GB!
> > However, in Enterprise Manager, if I go to the "Memory" tab of the
> > instance properties I can't increase the memory beyond 2147483647
> > (which I assume is around 2GB).
> No, that is a little over 2TB, as the unit of the parameter is MB
> (or rather 2^20 bytes, I guess).
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland, your're absolutely right. I am either blind or stupid (or
possibly a combination of both)!

Thanks for pointing this out :-) think I'll go back to washing dishes
or something...

Cheers,

Ian