MS SQL Server shared hosting resources

Question

I have a general question regarding use of MS SQL Server in a shared hosting environment.

I a shared hosting environment, where the MS SQL Instance hosts multiple databases from multiple customers (in my example the server contains aprrox. 70 databases from different customers).

In this scenario, can a badly designed database of a customer affect the performance of the databases of other customers? Could it for instance be eating much of the CPU power causing the other databases to suffer?

or are the resources of the server managed by MS SQL Server in a way this could not occur?

For reference the server in question has 10 processors and about 24GB RAM

Answer 1

of course

Think of it like this: Can one badly designed app drain your whole phone's battery?

To figure it out where your performance problems are, you have two options:

• Grab a free trial of a SQL Server monitoring tool (SQL Sentry works well enough)
• Grab a bunch of free scripts and piece together the analysis yourself

You didn't mention which version or edition of SQL Server you're on, but you can probably use:

• First Responder Kit
• sp_WhoIsActive
• Erik Darling's amazing free scripts

And come to some reasonable conclusions about where your problems are.

Answer 2

...are the resources of the server managed by MS SQL Server in a way this could not occur?

Just for completeness, it is possible to limit the resources available between databases indirectly by using a feature called Resource Governor.

Resource Governor essentially is a way to divide up the instance's available hardware resources into separate groups that different Logins on the server can be assigned to, such that the incoming sessions from those Logins are limited by the provisioning of those groups. Since the Logins in a shared hosting environment unlikely have access to databases they shouldn't have access to (i.e. the customers' Logins should only have access to their own databases), Resource Governor could indirectly limit the resource consumption by database in this scenario.

That being said, I doubt that this feature is in use, as I've never come across anyone actually using it, and I believe it's an Enterprise Edition only feature.

Answer 3

can a badly designed database of a customer affect the performance of the databases of other customers?

Yes, the SQL Server does not reserve resources for each DB. That's one of reasons reporting workloads are often run on different machine than application itself. (Because you don't want the long running reporting queries to contend for resources with quick queries of your end users.)

However, if all your databases have similar type of workload, one DB requiring much more resources is hardly different than the very same DB requiring those resources on dedicated machine - you still need to address the issue somehow.