I recently inherited an environment with around 4000+ databases distributed between 21 instances on 3 huge physical servers. Databases are distributed between different instances based on the name, so some instances are a few times bigger than others. Schema is identical for all databases, but data is not. Some databases are just few MB and other are 400+GB.
Management is considering whether we should try to keep as many databses as possible on each instance and have fewer huge instances. Another idea is to keep adding small instances to existing environment or even reduce the number of databases in each existing instance and increase the number of instances.
Administration wise its probably easier to have fewer big instances than many small instances. On the other hand if something happens to big instance then all databases on it will be affected, so it may be safer to have many small instances.
What would be better from the performance point of view? What other factors should I take into account? How many databases should I aim to keep on each instance? I know that it depends, but do you have some rough estimates? For example is 500 databases on one instance too many? If you need any more information please let me know.
I forgot to mention that all these databses are production databses of different clients and so all of them are equally important. Development, testing etc are on different servers.
Best Answer
A single SQL Server instance knows how to manage its memory, CPU and IO in the most optimal way. Two (or more) SQL Server instances cannot cooperate to coordinate usage of shared resources. On this grounds, a single instance is more performant than more instances. If you split into 2 (or more) instances, then it would be required to partition the resources between them (ideally via virtualization) so the instances do not start stepping on each other toes.
To give a concrete example, consider a query memory grant. This is a reservation against the buffer pool. Multiple queries can launch into execution, as long as their total grant does not exceed the allowed memory. A memory grant is not allocated immediately, so the BP is not evicted, but instead used incrementally, and most often not all the reservation is used (reservation is computed for worst case). On many instances, a single instance can hit its cap of the total memory grant and queue queries although the total system has the capability to run more. On a single instance, the total capability has to be hit, so a tenant can survive a spike better.
The biggest drawback and danger of instance consolidation is the noisy neighbor syndrome. If one Tennant takes on a constant basis more resources from the common pool, it will create a poor experience for other tenants as their workload can be starved of required resources and the system appear slow for everybody. On single instances you would use Resource Governor to control fairness between tenants, but the control is not complete. Using virtualization for isolation gives much better control. Using multiple instances on same physical OS is not ideal, imho it gives worse control than single instance Resource Governance and does not provide anywhere near the isolation offered by virtualization. My opinion.
My advice is therefore: either split at VM level, or consolidate to single instance (per physical server, obviously). Also, consider Azure SQL DBs instead, depending on some factors it may be better suited for your scenario.