Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Clustering / October 2005

Tip: Looking for answers? Try searching our database.

Memory Config - AWE, PAE, 3GB

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RomM - 24 Oct 2005 21:19 GMT
I have a cluster with 16 gb RAM on each server, one instance on each server
and no other applications on either server.  Do I set the max server memory
to 6 gig, 7 gig or  8 gig for each server?  I would think 7 gig, if I had a
failover I would have 7+7 +2 (for the OS).  Do I need to consider 2 gig for
the OS when setting max server memory?

Also, does AWE need to be set manually after each boot.  I ask this because
BOL (AWE Enabled Option) states it is strongly recommended to set max server
memory EACH TIME AWE is enabled.

thanks
Tom Moreau - 25 Oct 2005 12:03 GMT
7GB should be good for each instance.  You don't have to set max server
memory after each reboot.  Once you've turned on AWE and set max server
memory, you simply have to stop and start SQL Server the one time.
Subsequent reboots don't need any human intervention.

Signature

  Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.

I have a cluster with 16 gb RAM on each server, one instance on each server
and no other applications on either server.  Do I set the max server memory
to 6 gig, 7 gig or  8 gig for each server?  I would think 7 gig, if I had a
failover I would have 7+7 +2 (for the OS).  Do I need to consider 2 gig for
the OS when setting max server memory?

Also, does AWE need to be set manually after each boot.  I ask this because
BOL (AWE Enabled Option) states it is strongly recommended to set max server
memory EACH TIME AWE is enabled.

thanks
RomM - 25 Oct 2005 12:57 GMT
Thanks

Now I am a little confused.  Do I need the 3 gb switch.  I know that limits
the OS to 1 gb.  So under my scenerio below am I not losing 1 gig (7 to each
server leaves 2).   My questions:

How much does the OS need, 1 or 2 gig (under 16 gig RAM per server)?  

If 2 gig as under my below scenerio do I need to set the 3 gb switch?

thanks

> 7GB should be good for each instance.  You don't have to set max server
> memory after each reboot.  Once you've turned on AWE and set max server
[quoted text clipped - 12 lines]
>
> thanks
Tom Moreau - 25 Oct 2005 13:19 GMT
Check out:

http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech

Signature

  Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinpub.com
.

Thanks

Now I am a little confused.  Do I need the 3 gb switch.  I know that limits
the OS to 1 gb.  So under my scenerio below am I not losing 1 gig (7 to each
server leaves 2).   My questions:

How much does the OS need, 1 or 2 gig (under 16 gig RAM per server)?

If 2 gig as under my below scenerio do I need to set the 3 gb switch?

thanks

"Tom Moreau" wrote:

> 7GB should be good for each instance.  You don't have to set max server
> memory after each reboot.  Once you've turned on AWE and set max server
[quoted text clipped - 18 lines]
>
> thanks
RomM - 25 Oct 2005 13:51 GMT
Thanks for the response.  But it does not give a persfomance recommendation.  
Does the OS need more than 1 gig to operate efficiently when it has 16 gig of
RAM?

One other question, As mentioned we are moving to a cluster from 4 servers.  
Is there any advantage of creating seperate instaces on the cluster rather
than just combining the databases on 2 servers into one instance on the same
on the other server in the cluster?

> Thanks
>
[quoted text clipped - 24 lines]
> >
> > thanks
Geoff N. Hiten - 25 Oct 2005 13:51 GMT
The original recommendation by Microsoft was to use /3GB only with 16GB or
less physical RAM in a system.  After some extensive research, the
recommendation was changed to 12GB.  Above 12GB the system needs more OS
memory to manage the PAE/AWE memory region.  I would leave 7GB per instance,
unless you know one instance needs much more memory than the other one, in
which case do not exceed 14GB allotted to both instances combined.

Reboots will not affect maximum memory settings or AWE enabled setting.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> Thanks
>
[quoted text clipped - 32 lines]
>>
>> thanks
RomM - 25 Oct 2005 14:03 GMT
That is exactly what I needed to know, I appreciate it.

One other question, As mentioned we are moving to a cluster from 4 servers.  
Is there any advantage of creating seperate instaces on the cluster rather
than just combining the databases on 2 servers into one instance on the same
on the other server in the cluster?

> The original recommendation by Microsoft was to use /3GB only with 16GB or
> less physical RAM in a system.  After some extensive research, the
[quoted text clipped - 41 lines]
> >>
> >> thanks
Geoff N. Hiten - 25 Oct 2005 14:09 GMT
Not so much an advantage either way, just different options.  Multiple
instances allows you to spread the load out onto multiple hosts, but can
bite you if you end up collapsing the instances onto one node during peak
activity times.  Typically, instances are used to separate functional areas,
separate development and production systems, or to implement specific
security models within each instance.  Unless you have one of those needs, a
single instance would be a simpler solution.  I have built and managed
systems both ways depending on what was needed.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> That is exactly what I needed to know, I appreciate it.
>
[quoted text clipped - 57 lines]
>> >>
>> >> thanks
Anthony Thomas - 26 Oct 2005 13:33 GMT
A couple of things:

The 4 GB tuning is a "VIRTUAL" management, not physical.  Each process has a
4 GB virtual address space, that could be served by physical ram or swap
space.  Of that 4 GB, by default, 2 GB is dedicated to Private, or Kernel
mode, space, while the other is for User mode space.  The /3GB switch says
that "EACH" process can now address up to 3 GB User Mode space, but now only
1 GB of Kernel mode, or Private, space.

There is a performance gain for running multiple instances on a single node
for 32-bit architectures in that each instance gets its own User mode
address space from which the Procedure Cache and the other 4 memory managers
are carved, which is limited to the 2 or 3 GB lower memory area.

However, for consolidation and VLDB purposes, Microsoft has shown that a
single instance will typically outperform multiples, unless you begin to run
into this 2-3 GB limitation of the proc cache.  But, then, the decision
should be to upgrade to a 64-bit Itanium platform instead of multiple
instances strictly because each SQL Server per processor license costs you
the same regardless if it is running on an x86 or IA64 cpu.

The following site has tons of information on server consolidation, 32 and
64-bit architecture studies, SQL I/O and SAN information.

Scalability and Very Large Database (VLDB) Resources
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.mspx

Sincerely,

Anthony Thomas

> Not so much an advantage either way, just different options.  Multiple
> instances allows you to spread the load out onto multiple hosts, but can
[quoted text clipped - 66 lines]
> >> >>
> >> >> thanks
RomM - 31 Oct 2005 21:15 GMT
Thank you for the response Geoff

I was mistaken, I actually only have 12 gig of memory on each server.  So in
this case I should allot 5.5 gigs to each server and 1 gig is enough for the
OS?

I should now use the /3GB switch and set the max server memory to 5.5 for
each server.

thanks

> Not so much an advantage either way, just different options.  Multiple
> instances allows you to spread the load out onto multiple hosts, but can
[quoted text clipped - 66 lines]
> >> >>
> >> >> thanks
Anthony Thomas - 26 Oct 2005 13:15 GMT
Yes, you need to consider how much space to leave for the OS.

You only need to set Max Server Memory whenever you "enable" AWE, not every
time you reboot.  So, when you first run sp_configure 'awe', 1, then also
set Max Server Memory and restart SQL Server.

Sincerely,

Anthony Thomas

> I have a cluster with 16 gb RAM on each server, one instance on each server
> and no other applications on either server.  Do I set the max server memory
[quoted text clipped - 7 lines]
>
> thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.