multiple instance failover Cluster + awe
Martin
I'm just trying to optimize my sql servers memory
configuration.
We have an active/active cluster. In node A we have 2 sql
instances and in Node B 3 sql instances.
Each node have 8GB ram.
I don't know how to set memory for best performance.
Should I enable AWE?
Should I use the /3gb in boot.ini?
I know that if I use AWE I should set the MAX SERVER
MEMORY option and set them in such a way that all the sql
instances fit in one node, but if I have to, whats the
point of enabling AWE?
Thanks for any thoughs, comments or suggestions and for
your time.
Uttam Parui[MS]
complex and can be confusing. I am trying to give you detailed answers to
your qs. Hope that helps. Also, I have included additional resources that
you may be helpful.
QS. We have an active/active cluster. In node A we have 2 sql instances and
in Node B 3 sql instances.
Each node have 8GB ram.I don't know how to set memory for best performance.
.1. Should I enable AWE?
Ans. If you want SQL Server to use more than 3Gb of memory then use AWE.
.2. Should I use the /3gb in boot.ini?
Ans. Use of /3gb switch will allow SQL Server to use up to 3gb of available
memory.
.3. I know that if I use AWE I should set the MAX SERVER MEMORY option and
set them in such a way that all the sql
instances fit in one node, but if I have to, whats the point of enabling
AWE?
Ans. You enable AWE to enable SQL Server 2000 to use more than 3GB of
memory rather than a default maximum of 2GB or 3GB memory if you use /3gb
switch.
But remember, if AWE is enabled and max server memory has not been set and
there is at least 3 GB of free memory available on the node, then the
instance acquires almost all of the available memory and may leave only up
to 128 MB of memory free. To avoid memory starvation we need to specify
max server memory.
In a multiple-instance SQL Server 2000 failover cluster, the scenario
becomes more complex. With up to 16 instances that can
be active at a time on one node, how does one effectively manage memory?
First and foremost, ensure that all servers have the
same amount of memory, and that it is enough to handle the instances that
could potentially fail to that node.
If AWE memory is enabled, max server memory must be set in a
multiple-instance cluster to prevent starving the server node,
as shown in example two that follows. Consider any other processes that may
be running on the server, as well as overhead for
the operating system.
Example One: Two Instances of SQL Server, Noncapped Memory
Think of your cluster nodes as two glasses of water. Both glasses have a
maximum capacity of 4 ounces. Glass A and Glass B
contain 3 ounces of water each. If you pour the contents of Glass B into
Glass A, only 1 ounce will fit before an overflow of
the remaining 2 ounces occurs. Similary, if the failover node does not have
the physical memory to support the second
instance of SQL Server 2000, paging to disk will occur because SQL Server
2000 is looking for more memory than is physically
available. The server will now be short of resources, potentially causing
the node to become unresponsive.
Example Two: Two Instances of SQL Server 2000, Capped Memory
Again, think of your two cluster nodes as glasses of water. Both glasses
have a maximum capacity of 8 ounces. Glass A and
Glass B contain 3 ounces of water each. If you pour the contents of Glass B
into Glass A, Glass A can handle the entire
amount of liquid with no overflow. From a SQL Server perspective, for this
example to work, AWE memory must be enabled, and
each instance must use the sp_configure stored procedure max server memory
option to cap memory on each instance at 3 GB. In
the event of the failover, there is still 2 GB of memory left for the
operating system and any other processes running.
So coming to your configuration, say two sql instances on nodeA and nodeB
have 3GB of memory configured using AWE and the
third instance on nodeB uses dynamic memory (does not use AWE) and say it
is currently using 1.5GB of memory. Say a problem
occurs on NodeA, causing a failover. The two instances on nodeA will try to
restart on nodeB. What will happen ? Can it start
? nodeB has 8GB of memory and the three instances on nodeb already uses
3+3+1.5=7.5GB of memory. From where will the two
instances on nodeA get 3GB of memory each ? So we cannot predict what will
happen. You are risking memory starvation of the
OS and this can affect ALL instances (i.e even the three instances that
were running on nodeb will be affected) and nodeb can
become unresponsive.
NOTE: If you never have to failover, things would work great. But once a
failover happened, all bets would be off. So, the
question is what is your goal ? Performance or Availability !! If you are
implementing a cluster, you are probably saying
that availablity is your highest goal.
Additional Information:
=====================
/3GB switch allows an application to utilize up to 3GB of virtual memory
rather than a default maximum of 2GB.
What if 3GB is not enough for an application ? Microsoft® SQL Server 2000
uses the Microsoft Windows® 2000 Address Windowing
Extensions (AWE) API to support very large memory sizes. SQL Server 2000
can use as much memory as Windows 2000 Advanced
Server or Windows 2000 Datacenter Server allows.
Instances of SQL Server 2000 do not dynamically manage the size of the
address space when you enable AWE memory. Therefore,
when you enable AWE memory and start an instance of SQL Server 2000, one of
the following occurs:
* If sp_configure max server memory has been set and there are at least 3
gigabytes (GB) of free memory available on the
computer, the instance acquires the amount of memory specified in max
server memory. If the amount of memory available on the
computer is less than max server memory (but more than 3 GB), then the
instance acquires almost all of the available memory
and may leave only up to 128 megabytes (MB) of memory free.
* If max server memory has not been set and there is at least 3 GB of free
memory available on the computer, then the
instance acquires almost all of the available memory and may leave only up
to 128 MB of memory free.
* If there is less than 3 GB of free memory available on the computer,
memory is dynamically allocated and, regardless of the
parameter setting for awe enabled, SQL Server will run in non-AWE mode.
Additional Resources
===================
Microsoft Whitepaper - SQL Server 2000 Failover Clustering (see topic
Memory Configuration in this whitepaper)
http://www.microsoft.com/SQL/techinfo/administration/2000/failovercluster.as
p
HOW TO: Configure Memory for More Than 2 GB in SQL Server
http://support.microsoft.com/?id=274750
SQL Server 2000 Books Online Topics
- Managing AWE Memory
- awe enabled Option
- Creating a Failover Cluster
Chapter 14 in "Microsoft SQL Server 2000 High Availability (MS Press)"
ISBN 0-7356-1920-4
HTH
Uttam Parui
SQL Server Developer Support Engineer, MCDBA, MCSE, MCT
Product Support Services
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.