SQL Server – Performance Implications of Multiple Smaller Databases vs Single Larger Database

performancesql server

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?

Best Answer

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.

Related Question