| Groups | Blog | Home |
sql server clustering : 2003 & SQL 2000 Clustering Memory
Is 6GB the total memory? How much do you want each instance of SQL Server
to use? They can not both have 6GB. If 6GB is all you have then you probably should not use AWE since the memory is no longer dynamic. If you use /3GB on each instance they will each have 3GB to use but that leaves 0 for the OS. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message news:1ABCEDC3-4218-4CCF-AE80-B4B12A3E11B1@microsoft.com... >I see that there was a another query about this. But to confirm > If I wish to have active\active SQL on a server with 6Gb of memory should > I > use > > AWE enabled > PAE enabled > > But not /3Gb (even though there are articles which state that you should > for > 4 to 16Gb of memory server) > > What recomendations are there for the AWE setting so that SQL can > failover? > > -- > Thanks for any help, > Alistair
I see that there was a another query about this. But to confirm
If I wish to have active\active SQL on a server with 6Gb of memory should I use AWE enabled PAE enabled But not /3Gb (even though there are articles which state that you should for 4 to 16Gb of memory server) What recomendations are there for the AWE setting so that SQL can failover? -- Thanks for any help,
That is not my understanding of how the /3GB switch actually works. When you
add the /3GB to the boot.ini file it allows ALL applications to use 3GB of working memory and the OS then only gets 1GB (normally is 2GB for App, 2 GB for OS). It does not simply give 3GB to each SQL Instance. If you run WordPad, the two SQL instances and the calculator they will all get 3GB of working memory. As you can see from this example, paging would occur if both SQL's actually need that much memory. Cheers, Rod MVP - Windows Server - Clustering http://www.nw-america.com - Clustering Website http://www.msmvps.com/clustering - Blog [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OEx%23Yb4YFHA.3780@tk2msftngp13.phx.gbl... > Is 6GB the total memory? How much do you want each instance of SQL Server > to use? They can not both have 6GB. If 6GB is all you have then you > probably should not use AWE since the memory is no longer dynamic. If you > use /3GB on each instance they will each have 3GB to use but that leaves 0 > for the OS. > > -- > Andrew J. Kelly SQL MVP > > > "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message > news:1ABCEDC3-4218-4CCF-AE80-B4B12A3E11B1@microsoft.com... >>I see that there was a another query about this. But to confirm >> If I wish to have active\active SQL on a server with 6Gb of memory should >> I >> use >> >> AWE enabled >> PAE enabled >> >> But not /3Gb (even though there are articles which state that you should >> for >> 4 to 16Gb of memory server) >> >> What recomendations are there for the AWE setting so that SQL can >> failover? >> >> -- >> Thanks for any help, >> Alistair > >
I didn't think that is what I stated. Yes each application (in this case
SQL Server) will be able to use up to 3GB of memory. That memory is still dynamic and each instance will only use it as it needs it. The point I was trying to make was there is a real possibility that both instances will use upwards of 3GB. That means they may spend a lot of time allocating and deallocating memory between each other and the OS. SQL Server will always try to leave some memory for the OS (when not using AWE) so that paging does not occur but in this case it very well may. -- Andrew J. Kelly SQL MVP "Rodney R. Fournier [MVP]" <rod@die.spam.die.nw-america.com> wrote in message news:%23HBx3iIZFHA.2400@TK2MSFTNGP10.phx.gbl... [quoted text, click to view] > That is not my understanding of how the /3GB switch actually works. When > you add the /3GB to the boot.ini file it allows ALL applications to use > 3GB of working memory and the OS then only gets 1GB (normally is 2GB for > App, 2 GB for OS). It does not simply give 3GB to each SQL Instance. If > you run WordPad, the two SQL instances and the calculator they will all > get 3GB of working memory. As you can see from this example, paging would > occur if both SQL's actually need that much memory. > > Cheers, > > Rod > > MVP - Windows Server - Clustering > http://www.nw-america.com - Clustering Website > http://www.msmvps.com/clustering - Blog > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OEx%23Yb4YFHA.3780@tk2msftngp13.phx.gbl... >> Is 6GB the total memory? How much do you want each instance of SQL >> Server to use? They can not both have 6GB. If 6GB is all you have then >> you probably should not use AWE since the memory is no longer dynamic. >> If you use /3GB on each instance they will each have 3GB to use but that >> leaves 0 for the OS. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message >> news:1ABCEDC3-4218-4CCF-AE80-B4B12A3E11B1@microsoft.com... >>>I see that there was a another query about this. But to confirm >>> If I wish to have active\active SQL on a server with 6Gb of memory >>> should I >>> use >>> >>> AWE enabled >>> PAE enabled >>> >>> But not /3Gb (even though there are articles which state that you should >>> for >>> 4 to 16Gb of memory server) >>> >>> What recomendations are there for the AWE setting so that SQL can >>> failover? >>> >>> -- >>> Thanks for any help, >>> Alistair >> >> > >
I dealt with the exact same thing and unfortunately do not have any good
information for you. You can put in the boot switched to your hearts content, but until you set SQL to AWE enabled you won't gain any benefit. The problem is, once you set SQL AWE enabled it can no longer dynamically set it's memory. So say you have 2 nodes with 6 GB of RAM and set both to AWE enabled. SQL will grab up all the memory it can when it starts up. The probelm occurs when you go to fail node A over into node B. Node A will not start because there's not enough memory there for it to start. You can get around this by setting max_memory so there's enough for each server to run on the same node, but then you're wasting the memory. So in your case, you'd need to set max_memory on each SQL server at 2.5 GB so if they both fail into the same node they only use 5 GB, leaving 1 GB free for the SO. There's a good article detailing this here. http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_3.asp Check out the bottom of this page discussing memory. Hope it helps. [quoted text, click to view] "Alistair Keay" wrote:
> I see that there was a another query about this. But to confirm > If I wish to have active\active SQL on a server with 6Gb of memory should I > use > > AWE enabled > PAE enabled > > But not /3Gb (even though there are articles which state that you should for > 4 to 16Gb of memory server) > > What recomendations are there for the AWE setting so that SQL can failover? > > -- > Thanks for any help,
Thanks for your posts.
The total server memory is 6 Gb so that needs to be shared with the OS & SQL instances. Oringinally the requirement was for an active passive SQL cluster. (Servers to have total 4 Gb each) It was later decided that a non bussines critical SQL instance should be run on the other node so a further 2 Gb was specified on each server bringing the total up to 6Gb on each server. From your comments I assume you wouldn't recommned using AWE. Thanks again for any comments -- Thanks for any help, Alistair [quoted text, click to view] "Andrew J. Kelly" wrote:
> I didn't think that is what I stated. Yes each application (in this case > SQL Server) will be able to use up to 3GB of memory. That memory is still > dynamic and each instance will only use it as it needs it. The point I was > trying to make was there is a real possibility that both instances will use > upwards of 3GB. That means they may spend a lot of time allocating and > deallocating memory between each other and the OS. SQL Server will always > try to leave some memory for the OS (when not using AWE) so that paging does > not occur but in this case it very well may. > > -- > Andrew J. Kelly SQL MVP > > > "Rodney R. Fournier [MVP]" <rod@die.spam.die.nw-america.com> wrote in > message news:%23HBx3iIZFHA.2400@TK2MSFTNGP10.phx.gbl... > > That is not my understanding of how the /3GB switch actually works. When > > you add the /3GB to the boot.ini file it allows ALL applications to use > > 3GB of working memory and the OS then only gets 1GB (normally is 2GB for > > App, 2 GB for OS). It does not simply give 3GB to each SQL Instance. If > > you run WordPad, the two SQL instances and the calculator they will all > > get 3GB of working memory. As you can see from this example, paging would > > occur if both SQL's actually need that much memory. > > > > Cheers, > > > > Rod > > > > MVP - Windows Server - Clustering > > http://www.nw-america.com - Clustering Website > > http://www.msmvps.com/clustering - Blog > > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > > news:OEx%23Yb4YFHA.3780@tk2msftngp13.phx.gbl... > >> Is 6GB the total memory? How much do you want each instance of SQL > >> Server to use? They can not both have 6GB. If 6GB is all you have then > >> you probably should not use AWE since the memory is no longer dynamic. > >> If you use /3GB on each instance they will each have 3GB to use but that > >> leaves 0 for the OS. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message > >> news:1ABCEDC3-4218-4CCF-AE80-B4B12A3E11B1@microsoft.com... > >>>I see that there was a another query about this. But to confirm > >>> If I wish to have active\active SQL on a server with 6Gb of memory > >>> should I > >>> use > >>> > >>> AWE enabled > >>> PAE enabled > >>> > >>> But not /3Gb (even though there are articles which state that you should > >>> for > >>> 4 to 16Gb of memory server) > >>> > >>> What recomendations are there for the AWE setting so that SQL can > >>> failover? > >>> > >>> -- > >>> Thanks for any help, > >>> Alistair > >> > >> > > > > > >
Thanks. Quite a good article.
I will follow your advice and test. I will use AWE with memory capped on SQL as per your advice. I will enable PAE. I am still not clear however if I should use the /3Gb switch. For the memory I have I have found two technet articles _ one which says I should and the other which says I shouldn't. -- Thanks for any help, Alistair [quoted text, click to view] "Chris" wrote:
> I dealt with the exact same thing and unfortunately do not have any good > information for you. You can put in the boot switched to your hearts > content, but until you set SQL to AWE enabled you won't gain any benefit. > The problem is, once you set SQL AWE enabled it can no longer dynamically set > it's memory. > > So say you have 2 nodes with 6 GB of RAM and set both to AWE enabled. SQL > will grab up all the memory it can when it starts up. The probelm occurs > when you go to fail node A over into node B. Node A will not start because > there's not enough memory there for it to start. You can get around this by > setting max_memory so there's enough for each server to run on the same node, > but then you're wasting the memory. So in your case, you'd need to set > max_memory on each SQL server at 2.5 GB so if they both fail into the same > node they only use 5 GB, leaving 1 GB free for the SO. > > There's a good article detailing this here. > http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_3.asp > > Check out the bottom of this page discussing memory. Hope it helps. > > "Alistair Keay" wrote: > > > I see that there was a another query about this. But to confirm > > If I wish to have active\active SQL on a server with 6Gb of memory should I > > use > > > > AWE enabled > > PAE enabled > > > > But not /3Gb (even though there are articles which state that you should for > > 4 to 16Gb of memory server) > > > > What recomendations are there for the AWE setting so that SQL can failover? > > > > -- > > Thanks for any help,
If you don't enable /3GB you can only use 2GB per instance.
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message news:1B707F32-9D8C-4A0E-9DE0-B376A88728CD@microsoft.com... > Thanks. Quite a good article. > > I will follow your advice and test. > I will use AWE with memory capped on > SQL as per your advice. > > I will enable PAE. > > I am still not clear however if I should use the /3Gb switch. For the > memory > I have I have found two technet articles _ one which says I should and the > other which says I shouldn't. > > > > > -- > Thanks for any help, > Alistair > > > "Chris" wrote: > >> I dealt with the exact same thing and unfortunately do not have any good >> information for you. You can put in the boot switched to your hearts >> content, but until you set SQL to AWE enabled you won't gain any benefit. >> The problem is, once you set SQL AWE enabled it can no longer dynamically >> set >> it's memory. >> >> So say you have 2 nodes with 6 GB of RAM and set both to AWE enabled. >> SQL >> will grab up all the memory it can when it starts up. The probelm occurs >> when you go to fail node A over into node B. Node A will not start >> because >> there's not enough memory there for it to start. You can get around this >> by >> setting max_memory so there's enough for each server to run on the same >> node, >> but then you're wasting the memory. So in your case, you'd need to set >> max_memory on each SQL server at 2.5 GB so if they both fail into the >> same >> node they only use 5 GB, leaving 1 GB free for the SO. >> >> There's a good article detailing this here. >> http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_3.asp >> >> Check out the bottom of this page discussing memory. Hope it helps. >> >> "Alistair Keay" wrote: >> >> > I see that there was a another query about this. But to confirm >> > If I wish to have active\active SQL on a server with 6Gb of memory >> > should I >> > use >> > >> > AWE enabled >> > PAE enabled >> > >> > But not /3Gb (even though there are articles which state that you >> > should for >> > 4 to 16Gb of memory server) >> > >> > What recomendations are there for the AWE setting so that SQL can >> > failover? >> > >> > -- >> > Thanks for any help, >> > Alistair
Thank you all for your help _ much appreciated.
-- Thanks for any help, Alistair [quoted text, click to view] "Andrew J. Kelly" wrote:
> If you don't enable /3GB you can only use 2GB per instance. > > > -- > Andrew J. Kelly SQL MVP > > > "Alistair Keay" <AlistairKeay@discussions.microsoft.com> wrote in message > news:1B707F32-9D8C-4A0E-9DE0-B376A88728CD@microsoft.com... > > Thanks. Quite a good article. > > > > I will follow your advice and test. > I will use AWE with memory capped on > > SQL as per your advice. > > > > I will enable PAE. > > > > I am still not clear however if I should use the /3Gb switch. For the > > memory > > I have I have found two technet articles _ one which says I should and the > > other which says I shouldn't. > > > > > > > > > > -- > > Thanks for any help, > > Alistair > > > > > > "Chris" wrote: > > > >> I dealt with the exact same thing and unfortunately do not have any good > >> information for you. You can put in the boot switched to your hearts > >> content, but until you set SQL to AWE enabled you won't gain any benefit. > >> The problem is, once you set SQL AWE enabled it can no longer dynamically > >> set > >> it's memory. > >> > >> So say you have 2 nodes with 6 GB of RAM and set both to AWE enabled. > >> SQL > >> will grab up all the memory it can when it starts up. The probelm occurs > >> when you go to fail node A over into node B. Node A will not start > >> because > >> there's not enough memory there for it to start. You can get around this > >> by > >> setting max_memory so there's enough for each server to run on the same > >> node, > >> but then you're wasting the memory. So in your case, you'd need to set > >> max_memory on each SQL server at 2.5 GB so if they both fail into the > >> same > >> node they only use 5 GB, leaving 1 GB free for the SO. > >> > >> There's a good article detailing this here. > >> http://www.sql-server-performance.com/rn_sql_server_clustering_2000_to_2005_3.asp > >> > >> Check out the bottom of this page discussing memory. Hope it helps. > >> > >> "Alistair Keay" wrote: > >> > >> > I see that there was a another query about this. But to confirm > >> > If I wish to have active\active SQL on a server with 6Gb of memory > >> > should I > >> > use > >> > > >> > AWE enabled > >> > PAE enabled > >> > > >> > But not /3Gb (even though there are articles which state that you > >> > should for > >> > 4 to 16Gb of memory server) > >> > > >> > What recomendations are there for the AWE setting so that SQL can > >> > failover? > >> > > >> > -- > >> > Thanks for any help, > >> > Alistair > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |