 | |
Active/Active cluster on 2005 - can I oversubscribe RAM now?
microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Active/Active cluster on 2005 - can I oversubscribe RAM now? -
01-03-2008
, 08:11 AM
#2
| |||
| |||
|
|
Recently, I was in a training course about the new features of 2005. The trainer said that 2005 dealt with active/active memory differently during a failover - as the secondary came online, it would start taking more memory, and the primary would release memory. So, on my two boxes with 16gb RAM, I allocated 10 for one node and 12 for the other (before we were at 6 & 8). However, when we had a failover it appeared that the secondary was going to take as much memory as it could - I had less than 1gb available by the time we "rolled" the secondary back to its original box. But, that begs the question - was that advice incorrect? On an active- active cluster on 2005, does the memory still need to be set to not oversubscribe? Thanks in advance. |
#3
| |||
| |||
|
|
I don't oversubscribe memory in such a setup. If you want to have predictable performance, I'd still recommend you ensure that the sum of the max server memory allocation for all the instances be below the available memory. And if that results in less than the required amount for a particular instance, add more memory to the nodes to accommodate it, and save yourself the headache. |
consequence, if MS had coded for it, so we did it. Mea culpa.
#4
| |||
| |||
|
|
Recently, I was in a training course about the new features of 2005. The trainer said that 2005 dealt with active/active memory differently during a failover - as the secondary came online, it would start taking more memory, and the primary would release memory. So, on my two boxes with 16gb RAM, I allocated 10 for one node and 12 for the other (before we were at 6 & 8). However, when we had a failover it appeared that the secondary was going to take as much memory as it could - I had less than 1gb available by the time we "rolled" the secondary back to its original box. But, that begs the question - was that advice incorrect? On an active- active cluster on 2005, does the memory still need to be set to not oversubscribe? Thanks in advance. |
#5
| |||
| |||
|
|
SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. |
question is, how would you write such a script? Have the instance
check the physical server name, and if it's not the right one then do
an sp_configure to lower the max server memory, with recompile? And I
assume that'd be a job on the node scheduled to run on startup?
Thanks,
Michael
#6
| |||
| |||
|
|
On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts... (AT) gmail (DOT) com> wrote: SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. Geoff, thanks for the clarification. I guess that the followup question is, how would you write such a script? Have the instance check the physical server name, and if it's not the right one then do an sp_configure to lower the max server memory, with recompile? And I assume that'd be a job on the node scheduled to run on startup? Thanks, Michael |
#7
| |||
| |||
|
|
On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts... (AT) gmail (DOT) com> wrote: SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. Geoff, thanks for the clarification. I guess that the followup question is, how would you write such a script? Have the instance check the physical server name, and if it's not the right one then do an sp_configure to lower the max server memory, with recompile? And I assume that'd be a job on the node scheduled to run on startup? Thanks, Michael |
#8
| |||
| |||
|
|
On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts... (AT) gmail (DOT) com> wrote: SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. Geoff, thanks for the clarification. I guess that the followup question is, how would you write such a script? Have the instance check the physical server name, and if it's not the right one then do an sp_configure to lower the max server memory, with recompile? And I assume that'd be a job on the node scheduled to run on startup? Thanks, Michael |
#9
| |||
| |||
|
|
On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts... (AT) gmail (DOT) com> wrote: SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. Geoff, thanks for the clarification. I guess that the followup question is, how would you write such a script? Have the instance check the physical server name, and if it's not the right one then do an sp_configure to lower the max server memory, with recompile? And I assume that'd be a job on the node scheduled to run on startup? Thanks, Michael |
#10
| |||
| |||
|
|
On Jan 3, 9:01 am, "Geoff N. Hiten" <SQLCrafts... (AT) gmail (DOT) com> wrote: SQL 2005 does have dynamic memory even when using AWE. However, the mechanism does not allow for dynamic memory balancing between instances. The OS signals all SQL instances that there is memory pressure and to reduce physical RAM usage. So, all instances try and shrink. When pressure is off, all instances get the same signal and can try to expand. The instances do not communicate to each other about their respective memory needs and usage, so there is no possibility of "balancing" memory. You can write a script that executes on a SQL failover event to reset maximum memory on SQL instances on the new node. Geoff, thanks for the clarification. I guess that the followup question is, how would you write such a script? Have the instance check the physical server name, and if it's not the right one then do an sp_configure to lower the max server memory, with recompile? And I assume that'd be a job on the node scheduled to run on startup? Thanks, Michael |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.