Too many SQL instances in SQL Server 2008 3 node cluster

Question

Hi All,

We are planning to have 6 to 7 instances in SQL Server 3 node cluster (All Active).
All the SQL instances are very critical & equally distributed among 3 nodes.

I have been asked that “what might be the consequences of having too many instances in 3 node cluster”<o:p></o:p>

I have shared my views on CPU and Memory bottleneck, is there anything else that needs to be taken care.<o:p></o:p>

Looking forward for your answers. Please be detailed in answers... :) :)

---

Ananth Kamath

Answer 1

Hello,

Instances will compete between each other for memory, and while this is happening the operating system could be left using less memory than recommended. This could have a great impact in performance.

My recommendation is balance memory usage across instances. Set “max server memory” option for each instance. Leave 4 GB for the operating system.

I would also recommend you to configure parallelism based on the select statement you will find on the following post:

http://blogs.msdn.com/b/sqltips/archive/2005/09/14/466387.aspx

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

What if one or more SQL instances failover to another node and you may see more number of SQL instances running on one of the nodes and competing for the resources and can end up having performance issues

Answer 3

Hi Alberto,

I have proposed the same, having more instances may lead to performance issue when other instances are made to run on same node. regarding MAXDOP it will be taken care depending upon the processors and application vendor will suggest what MAXDOP is best for the queries they are using.

Though I'm looking for more things which may lead to an issue of having more instances in 3 node cluster

Thank you as always...

---

Ananth Kamath

Answer 4

Hi Deep,

this is already considered... Anyway Thank you for your post... :)

---

Ananth Kamath