What are the performance implications of running multiple smaller DBs instead of a single larger DB on a server?

Question

Our database architecture allows multiple 'customers' to exist in the same database, yet we split them into mulitple database for administrative reasons [patching, backup, etc.]

Question 1

What would the performance implications be if we consolidated the customers into a single DB?

Question 2

We current have multiple customer in each DB, but we may have 10 in each and say 5 database; so if we consolidated the DBs we'd just have one DB with 50 customers; would that make much difference to performance?

Answer 1

As others have indicated in the comments, it's difficult to form an answer to this question without an understanding of the application. It depends, it really really does.

The nature of the question (and answer) also changes on the basis of the physical environments i.e. multiple databases on a single server or spread across several? Is a customer “typical” or do some consume a disproportionate percentage of server resources? In 3 years, will there be 50 customers or 50000?

That said, let’s have a crack at it.

What would the performance implications be if we consolidated the customers into a single DB?

Potential gains

• Reduction in plan cache memory. If you have 5 of the same database you have 5 copies of every execution plan.
• Improved buffer pool utilisation. Similar to above, any common data that you have in each database exists in the buffer pool for each database.
• Improved cpu/memory utilisation. Consolidating multiple servers to a single server removes the overhead of the operating system on each host.
• Possibly improved IO utilisation. Combining the smaller arrays allocated to each server might result in improved overall throughput by having higher capacity to deal with peaks.

Potential risks

• $$$. Multiple low spec servers are typically cheaper than an all mighty powerhouse. Spreading the load across utility servers can be cheaper.
• Expansion flexibility. When you have all customers on that all mighty powerhouse server and it runs out of steam, upgrading is difficult and complex.
• Locking/blocking/deadlocks. Any deficiencies in the database and application design are likely to be magnified in a single host environment.

From your comments, it sounds like this is early days for your software and company. So, I’d be looking for ways to maximise flexibility and minimise capital expenditure.

• Install an expandable iSCSI array. With separate storage, you can expand both raw GB capacity and IOPS in isolation from the servers.
• Make your customers data portable. Might be difficult to retrofit but if you can migrate a customer’s data from one system to another, you can move them between systems to better balance workloads.
• Tier your customers. Allocate each customer to a tier depending on their usage, perhaps 3 tiers initially. Analyse typical usage patterns for these tiers of customers and allocate them to server resources accordingly. It may be that you can accommodate 200 tier 3 customers on a single box, or 50 tier 2, or 10 tier 1.

Maybe virtualisation would be a better fit. Maybe cloud would work. Maybe hybrid cloud. Honestly, it really does depend. Call in an expert to help, they could save you a fortune.

Answer 2

One of the biggest drawbacks with lots of smaller (and smaller obviously being a relative size) databases, is log management. Log files are written to sequentially, not randomly. With one large database, you can dedicate your log file to a single disk array, giving maximum sequential performance. If you have 50 databases on that same array, the data will effectively be random IO, causing way worse performance.

The same goes for data files to some extent, but since they're written to asynchronously by the checkpoint process, it's less of an issue since it'll mostly be smaller sequential writes.

We're running a similar architecture and while I really enjoy the benefits in patchability and running different versions, I'd definitely go for a completely multi-tenant solution if I were to do it over.

Answer 3

What would the performance implications be if we consolidated the customers into a single DB?

Only you can answer this question. Not knowing your environment or your server configurations this would be difficult to predict. There are tools out there that can be used to stress test your current configurations to see what the limit is they can handle.

You should setup a script to gather the performance counters over a period of time so you have a good baseline of your systems. You will be able to use this to find your peak times of traffic for each one. You can do a quick Google search for "SQL Server performance counters" that can point you in the right direction. I am also sure stackexchange has many post on it as well.

EDIT This would be a good link to start with for the performance counters.

Answer 4

I know this question asks specifically about performance implications, and there are good answers here already, but I think it would be remiss to not mention a topic that hasn't been broached yet when talking about multi-tenant solutions: security.

When you hold data for multiple customers in a single database, you have to be extremely careful when it comes to security, not only in the database itself, but in the application(s) as well. Generally speaking, one customer should never be able to see another customer's data. I don't know your business, but this is usually the case.

When architecting an application, creating security boundaries is desirable, so that if when your team happens to make a small mistake, there's already a built-in fallback to prevent Bad Things from happening. A database is one type of security boundary.

This separation (a business requirement, which may or may not exist) needs to be balanced with your plan to manage the data, performance, etc., though security requirements can (and should) trump everything else if it's deemed important enough by the business.

Yes, it sounds like there's an echo in here, but it really does depend on your exact situation to make the best decision. All we can do is give you ideas to help you reach that conclusion.