memory.
On each active node, I am running a single named instance of sql server 2000
enterprise wiath latest svc packs.
The boot ini on the servers is currently configured with the /PAE switch only.
Each SQL named instance is defined to manage memory dynmaciclly. There are
no plans to add addtl memory to the servers.
I need to account for defined memory within each node to accomodate a
failover of either node to the other and run the resepctive SQL Server
instances.
What impact does the /PAE switch have on this particular configuration and
would it be better instead to use the /3GB switch? I believe that would
provide 3 gigs of memory to each sql server if running on the same node with
1 gig left for the O/S.
Would the /PAE switch not be necessary then?
thanks
Tom
/3GB, /PAE, and /AWE plus sp_configing your memory in SQL are great ideas,
but they all have to be monitored and test with your application, hardware,
and exact configuration. Have you called the hardware vendor to find out
what they suggest from a hardware stand point?
I am sure a true SQL DBA/MVP like Geoff will follow with a detailed SQL
explanation of the switches above for you
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
|||Right now, your SQL Instances get about 1.6GB of physical RAM each. They
should increase memory usage until they get to that level then stay there
long term.
/PAE lets the Operating System see all the physical memory in the servers.
It is necessary anytime a 32-bit OS has more than 4 GB of physical RAM.
I might enable AWE memory in SQL. If you do, you must set a maximum memory
value for SQL on a multi-instance cluster. Two SQL behaviors combine to
make this a necessity. First, AWE kills dynamic memory allocation. Memory
is allocated at service startup and never shrinks. Second, the maximum
memory value is set to the actual physical memory amount. SQL grabs
everything. You will need to set the actual memory values on both instances
so they can "stack" on the same server without overcommitting memory. Be
sure and leave some for the OS. I would recommend 3GB or so, but you can
watch the Memory | Available MBytes counter to check.
As for the /3GB switch, you could go that route. You won't replace the /PAE
switch, you will add this switch to the boot.ini startup options.
One option I have used in this situation was to set up the two instances
with asymmetrical memory allocation. If you know one server is under higher
load, you can allocate more memory to that instance. I ran a similar system
to yours with 4GB on one instance, 2GB on the other instance, and 2GB left
for the OS. The perfmon counter SQLServer:Buffer manager | Page Life
Expectancy will let you know relative memory pressure. Be sure to use a
fairly long observation time with the server under normal workload to
determine true memory pressure.
Any way you go, you will need to manually set memory levels and monitor to
see of you have overcommitted memory. However, SQL loves memory so you may
see some performance gains. Use the Page Life Expectancy counter to see the
difference before and after.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
|||The /3GB switch simply restricts the OS to 1GB of RAM, which allows
applications to address up to 3GB of RAM. The /PAE switch simply loads a
different NT kernel which contains the code to address RAM > 4GB.
The AWE setting in SQL Server works with the /PAE switch. If you enable
AWE, but don't turn on the /PAE switch, then you can not address the
extended memory space. So, in order to allow AWE to utilize the RAM above
4GB, you need to turn on the /PAE switch as well.
In terms of running multiple instances on a single machine, you need to
balance the RAM. (If your business requirements will allow performance
degradation in the event of a failover, then you don't necessarily need to
do this.) You control the maximum amount of RAM that SQL Server will
address by setting the max server memory config option.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom Frost" <TomFrost@.discussions.microsoft.com> wrote in message
news:269FF0A2-3F04-4767-8D67-15C9DC20AA95@.microsoft.com...
>I have a 2003 Enterprise Ed 2 node cluster with each node having 8 gig of
> memory.
> On each active node, I am running a single named instance of sql server
> 2000
> enterprise wiath latest svc packs.
> The boot ini on the servers is currently configured with the /PAE switch
> only.
> Each SQL named instance is defined to manage memory dynmaciclly. There are
> no plans to add addtl memory to the servers.
> I need to account for defined memory within each node to accomodate a
> failover of either node to the other and run the resepctive SQL Server
> instances.
> What impact does the /PAE switch have on this particular configuration and
> would it be better instead to use the /3GB switch? I believe that would
> provide 3 gigs of memory to each sql server if running on the same node
> with
> 1 gig left for the O/S.
> Would the /PAE switch not be necessary then?
> thanks
> Tom
>
sql
No comments:
Post a Comment