AWE and SQL Cluster

Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
SeanI would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
> >I would definately suggest using the AWE enabled option set to true.
> > especially if each node is dedicated to sql server. you will have to
> > restart the sql services to have the changes take place. check the
> > BOL: awe enabled option and the section managing awe memory.
> >
> > Sean T Shen wrote:
> >> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
> >> Server
> >> instance running). The SA has upgraded the memory of both nodes to 8G
> >> each.
> >> And I am tempted to use AWE to boost my SQL Server performance. The
> >> server
> >> is dedicated to running SQL Server.
> >>
> >> After AWE is enabled, can I configure my SQL Server max memory to
> >> something
> >> like 7G?
> >> Concern is, while it may run OK during normal operating mode, what
> >> will
> >> happen in case both SQL Servers are moved to one node?
> >>
> >> Thanks very much in advance.
> >> Sean
> >|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and release
>> memory from the operating system as needed. Dynamic memory allows you to
>> set a maximum amount of physical memory that the operating system can
>> allocate to SQL Server. This allows the remaining memory to be reserved
>> for other purposes, such as the operating system and other SQL Server
>> instances. Fixed memory allows you to set a specific amount of physical
>> memory that SQL Server can use. This amount neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance from
>> another node. If a server does not use a lot of memory compared to the
>> available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By doing
>> so, you leave sufficient memory for the failover to occur quickly and
>> successfully. If an instance uses all of the available physical memory,
>> failover will occur more slowly and can fail if memory is not released
>> quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegroups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of
>> a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate
>> memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the
>> cluster, you must determine the total amount of physical memory required
>> for
>> each server and determine the appropriate allocation of memory for each
>> SQL
>> Server instance. Performance of SQL Server is directly related to the
>> amount
>> of physical memory allocated to it by the operating system. SQL Server
>> provides two modes for memory management: dynamic and fixed. Dynamic
>> memory
>> allocation lets SQL acquire and release memory from the operating system
>> as
>> needed. Dynamic memory allows you to set a maximum amount of physical
>> memory
>> that the operating system can allocate to SQL Server. This allows the
>> remaining memory to be reserved for other purposes, such as the operating
>> system and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory
>> is available to support the failover of a SQL Server instance from
>> another
>> node. If a server does not use a lot of memory compared to the available
>> memory on the server, specify dynamic memory and set the maximum amount
>> of
>> memory that the operating system can allocate. By doing so, you leave
>> sufficient memory for the failover to occur quickly and successfully. If
>> an
>> instance uses all of the available physical memory, failover will occur
>> more
>> slowly and can fail if memory is not released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>> >I would definately suggest using the AWE enabled option set to true.
>> > especially if each node is dedicated to sql server. you will have to
>> > restart the sql services to have the changes take place. check the
>> > BOL: awe enabled option and the section managing awe memory.
>> >
>> > Sean T Shen wrote:
>> >> Hi, I have the following questions regarding AWE on SQL 2000 with
>> >> Windows
>> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> >> Server
>> >> instance running). The SA has upgraded the memory of both nodes to 8G
>> >> each.
>> >> And I am tempted to use AWE to boost my SQL Server performance. The
>> >> server
>> >> is dedicated to running SQL Server.
>> >>
>> >> After AWE is enabled, can I configure my SQL Server max memory to
>> >> something
>> >> like 7G?
>> >> Concern is, while it may run OK during normal operating mode, what
>> >> will
>> >> happen in case both SQL Servers are moved to one node?
>> >>
>> >> Thanks very much in advance.
>> >> Sean
>> >
>|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32? API
>> that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and
>> release memory from the operating system as needed. Dynamic memory
>> allows you to set a maximum amount of physical memory that the operating
>> system can allocate to SQL Server. This allows the remaining memory to
>> be reserved for other purposes, such as the operating system and other
>> SQL Server instances. Fixed memory allows you to set a specific amount
>> of physical memory that SQL Server can use. This amount neither grows
>> nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
--
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>