SQL Server 2005 Scalability and Performance - recommended limit on number of instances on a clustered environment?
This is a very tricky question and hard to stick to 1 or few solutions as answer, it depends!
Overall the say is if there isn’t any resource contention (from your pre-installation testing & analysis) and there is enough of each resource to go around, then there is usually no reason to set any restrictions on the server resource. The Operating System and SQL Server will share the available resources without any problems. However, if there is any resource contention, then depending on the resource and what you want to happen, the way that SQL Server and the Server Resource manage the contention might not give you the result you want. In this situation you should consider more testing (real-time or like-to-like configuration) on your pre-production environment to see whether any setting of restriction on the resources will do any better.
As it speaks the main contention you will see is memory usage on that server, by default each SQL Server instance configuration (dynamic memory settings) will take what memory it thinks it needs, up to the available physical memory. When there is memory contention, then sometimes one SQL Server instance won’t release memory as quickly as you might like. So in this case changing or considering the memory setting to MIN & MAX should have thorough testing of your application during busy times, where the slow performance issue is reported. After such testing you may need to configure maximum and minimum server memory settings to manually control this. As these are dynamic settings in SQL Server 2005, you can change them without a reboot and see an immediate effect. With the latest enhancement with Windows 2003 (even with 2008 version) and each SQL Server (version 2005 for discussion sake, even can consider the SQL Server 2008) instance does a great job of sharing the CPU between all threads. You feel this is great you see and may this is what you want, but what it does is allow all instances to have an equal share of the CPU, not a share based on how much work they have to get done. For discussin sake if you have both DSS & OLTP based applications within your environment then this is unacceptable and you may need set the configuration to OLTP system to hike the performance.
A short discussion leads to the disk I/O after the memory configuration, to check the contention. With recommended practices or based on your own configuration you can choose I/O system, from similar blog posts such as :
Then it comes to the configuration of setting up the layout of data and log files on to different physical disks with each on different I/O path. When it comes to a Clustered environment having multiple instance this is more complex. You need to consider the additional concerns such as instance failover, what do do and how resources are coping up to provide scalability & performance and this also highlights the resource requirements of the instances that may fail over to this node. As it complexes the topic I recommend to review the whitepapers such as "SQL Server 32bit Consolidation using a Clustered Environment" and "SQL Server Consolidation on the 64-Bit Platform".
Lastly, it comes to the number of instances that are supported by SQL Server, it is 50 (default + 49 named) when the environment is not a clustered. When SQL Server is clustered then you can have 25 as maximum number of instances, this is not a definitive or recommended value but it has to be or can be further restricted by additional limitations imposed by clustering. Again this circles around the same old saying, that is going to depend on the resources available on your server and the resources that each instance requires. You need to sumup the resource requirements such as CPU, Memory, I/O and load (number of users & number of times data is retrieved). Recently I was involved with one of the SQL 2005 Cluster configuration project and we have easily setup multiple instances by taking the mount point support, for the sake of scalability of resource usage. Still the availability needs to be considered such as if you get down to one node, one plus one still must equal two. If one plus one equals three, then your totaled-up instances have greater capacity than the node itself. By taking the points from above resources and links we were able to reduce the contention the allocated a particular instance (as dedicated one) for the busiest application, in order to avoid any confusion of resource allocation by the hardware itself.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.