February 14, 2011 at 8:49 am
mohsinabdul (2/14/2011)
Do you mean that 42 DBs will be less for 3 servers? i would prefer 3 a and 2 p but here we have cost constraints
Sorry dont under stand your response.
If you have 3 instances, you have 3 lots of system db's, 3 lots of sqlsvr.exe memory usage, 3 lots of clr buffer cache memory (and all the other memory buffers which use mem outside the core .exe)...
What I am saying is running 42 db's on 1 instance, uses less resources (mem and cpu) then 42 db's on 3 instances on the same box!
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 2, 2011 at 11:44 am
We have recently converted an Active/Passive cluster to an Active/Active cluster (SQL2005). I understand that if one of the nodes fails, the remaining node will have to carry all of the load. I'd like to run each of the Active nodes at better than 45% utilization. But, I'm concerned about the consequences of this if one of the nodes fails. For instance, if I plan for each node to run at 60% utilization, logically, a failure would result in a single node at 120% utilization. NOT GOOD.
Is there a way to restrict the resources available on the remaining node following a node failure? Sort of a dynamic resource governor...
Let's say no governor is available. If my 120% utilization fails to a single node, will it crash or just cause the applications to experience slower performance? I'd be fine with some degraded performance, but not a crash.
Thanks for your insights!
June 2, 2011 at 11:53 am
I guess the big question is do both instances run at 60% utilization every single second of every single day 24X7. My guess is no. If it does then you will see some slowness yes. If not, then most likely you won't see any problems.
On a SQL 2000 cluster I had an issue with the CPUs being pegged at 100% for hours on end and it would cause the cluster to fail over. This is because the heartbeat check would time out and it would think the other node is dead so it would fail the cluster over to the inactive node. We did increase the timeout which helped a lot. However, we fixed some bad code and also installed two more CPUs in the one node and licensed it that way.
June 3, 2011 at 4:54 am
ken.herring (6/2/2011)
We have recently converted an Active/Passive cluster to an Active/Active cluster (SQL2005). I understand that if one of the nodes fails, the remaining node will have to carry all of the load. I'd like to run each of the Active nodes at better than 45% utilization. But, I'm concerned about the consequences of this if one of the nodes fails. For instance, if I plan for each node to run at 60% utilization, logically, a failure would result in a single node at 120% utilization. NOT GOOD.Is there a way to restrict the resources available on the remaining node following a node failure? Sort of a dynamic resource governor...
Let's say no governor is available. If my 120% utilization fails to a single node, will it crash or just cause the applications to experience slower performance? I'd be fine with some degraded performance, but not a crash.
Thanks for your insights!
1) higher levels of windows server come with a resource management capability
2) enterprise SQL Server 2008+ also comes with a resource governor
3) outside of those two, you 'COULD' set processor affinity at the instance level after a failover, but I would be very leery of that solution.
I don't think any of those solutions have the ability to throttle IO though (could be wrong about windows server one).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply