I'm writing a research essay over distributed cloud-based systems, and one of the topics that I'm trying to touch on is potential solutions to high database utilization in a single-database multi-tenant architecture. I've been searching the web for some ideas, but for the most part I'm coming up empty handed.
For example, at my current employer, we have several instances that house a couple hundred databases each. Each customer gets their own database, and even with a single database per customer, it's not uncommon for SQL Server to drastically degrade in performance on a single database when that database is performing incredibly intensive processes (such as large queries in tables with millions of rows, complex joins, etc).
We've mitigated potential bottlenecks by giving every customer their own database, and this prevents us from degrading performance for everyone when only a single database is being heavily utilized.
What other techniques and strategies are there to offset these type of disaster scenarios in large multi-tenant databases?