A few huge instances or many small ones

Question

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.

Answer 1

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.

Answer 2

This isn't a full/distinct answer so much as a detailed explanation of why I favor @Remus's answer, specifically the VM recommendation, that is too long for a comment and not in any way communicated by my upvote of said answer.

---

I worked at a company that had a very similar environment: Federated Farm approach where there was a single data model used on 18 Production servers (each lower environment had far fewer, but still multiple, nodes) with one Instance per Server. The only difference was the data (well, and the Server / Instance name ;-). I believe they were even clustered in sets of 3 for High Availability. This started with SQL Server 2000 and continued through our upgrade to SQL Server 2005 years later. We also had a separate, single Instance "controller" node with a different data model that held common data (e.g. logins, SQL Server Agent MSX node, etc) as well as static / lookup data that was replicated out to the 18 federated servers.

That worked well enough, but when we were getting ready to upgrade to SQL Server 2012, there was a simultaneous push to virtualize, partially due to the new licensing model (Bye Bye, Mr. Per Slot; Hello Mr. How Many Core Packs Do I Need Again?). The upgrade cost was going to be prohibitively expensive, given our high multi-core servers. But -- and this might require paying for Software Assurance -- we could pay for the Core Packs once (for however many cores the server had), and then set up any number of VMs on those servers with each VM sharing the fully licensed server. I assume we also had to pay for VMWare for however many physical servers we did this on. But we ended up having many VMs, each with a single, default Instance.

This setup wasn't my idea, and in fact I did not have high-hopes for moving to VMs as I had always read that SQL Server should be on a physical server (this was way, way back, eons ago, in 2012 ;-). But, it seems that SQL Server was "certified" to work on certain versions of VMWare. And, looking back a few years later, it turned out to be a good move. We were even able to get rid of the clustering since they were able to set up VMWare to spin up a new VM and restore the OS and SQL Server install from a snapshot, if any VM crashed (data & log files were all on SAN-attached volumes).

Answer 3

You aren't at any hard database limit per instance.

When you have a lot of databases in an instance two things quickly run out; max worker threads and the Agent subsystem but both are easily increased. Another limitation that occurs is when one database goes nuts and starts churning pages of others out of the buffer pool.

The biggest reasons for separate instances are:

• Segregating troublesome applications that require instance-level options or have rampant security requirements.

• When you're testing or upgrading from one version or configuration to another.

• When you're desperate but need to patch instances separately; though there are shared and instance-unaware components plus OS patching which makes this still better done across servers rather than instances.

The problems you encounter from packing database too densely aside from what I covered in the beginning:

• Separating performance issues within an instance with many databases becomes extremely difficult. Doing it across multiple instances on the same server as well is nearly impossible.

• It's not too difficult to control or predict your backup schedule when it's triggered from one job iterating hundreds of databases on a server. It's much harder to do the same across hundreds of instances. It will be almost impossible to stop them from overlapping or to schedule them fairly.

• You'd have to be extremely carefully managing max server memory for each instance, and CPU affinity. And you would then not easily be able to integrate this with resource governor, which takes away your easiest management option.

For your purposes, and given those burdens, I would be for consolidating to one major instance per server (not to mention having a development environment). It just makes sense.

Answer 4

As long as the CPU, disk, and I/O levels do not max out for a given instance, you are in the safe zone. Static content that is not accessed regularly does not affect the normal performance of SQL Server.

There are security and compliance checks per instance to secure database services, and each server needs regular software patches. Also it is a best business practice to group databases by category {development, test, production} onto separate instances to keep development personnel from accessing live data.

This question has many different correct answers based on the priorities of a given organization.

Below is information on enterprise grade database security recommendations

Information Assurance Support Environment - Security Technical Implementation Guides (STIGs)