Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-03-09 : 17:46:33
|
| I have 3 physical servers each running one instance of SQL Server 2008. My IT dept has a very beefy server they would like to use for all 3 instances. I think it will be okay generally (each instance will be assigned plenty of RAM and its own set of disks), but there may be times when a bad query eats up crazy CPU. Is it better to use resource governor than to assign specific cores to each instance? Are both options risky? Might I run into a bottleneck at the bus/controller that chats with the disks? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-03-11 : 13:39:47
|
| No one can answer specifically.You need to baseline performance on all 3 to determine if this is feasible. Not just average loads, but peak loads too.You can't just say X is good so X+1 is better. And if you have bad queries that max out CPU, then you'll have trouble with all 3 instances. Should fix those queries, but that's another story.Also, need to consider what kind of load each has. I won't host a DW and an OLTP system on the same box. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-03-12 : 14:10:37
|
| Why wouldn't the resource governor or the processor affinity mask stop a query that maxes out "assigned processors" from affecting other instances? Isn't that the point of the resource governor?Obviously the bad queries should be fixed, but they're not always caught right away. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-03-12 : 14:26:13
|
| Affinity mask maybe, resource governor not so much. RG can limit the number of CPUs used but not which ones. If you set up affinity masks correctly then yes, you can keep each instance on separate processors. The problem with that is one processor being overloaded while the others could be idle. It also doesn't segregate memory by instance unless you use NUMA. If you really need to keep them separate it would be better to virtualize them.And...do you really need to keep them separate? What's the reason for keeping them as separate instances? |
|
|
|
|
|
|
1 server, 3 instances, resource contention?