How can I minimize impact of expensive queries in Database A on Databases B, C, D

Question

How can I configure SQL Server so that activity in 'Database A" has minimal impact on users in 'Database B'

The Context

Our (very large) production SQL Servers have a lot of databases on them running various "legacy apps", each database for a particular customer's instance of an application.

The Problem

If a "rogue user" runs a very expensive query--sometimes from a report, other times from the application--it will cause "the other databases" to slow to a crawl.

[I understand that the 'root problem' is the expensive queries; programmers should not allow them to run. Intensive reports should run off a separate reporting databases. Programmers should test on large data set, etc. My question pertains to 'isolating' impact of the databases on each other.]

I suspect, but I have not confirmed that the problem lies in the 'tempdb', the temporary area/database SQL Server uses for sorts it can't fit in memory.

I've seen posts pointing to spreading tempdb over multiple files. Would this help?

https://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-multiple-files-w01/#.VdzGxrNv4Uw

Answer 1

I would recommend taking a look at the resource governor... In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical IO, and memory. By using Resource Governor, you can:

1. Provide multitenancy and resource isolation on single instances of SQL Server that serve multiple client workloads. That is, you can divide the available resources on a server among the workloads and minimize the problems that can occur when workloads compete for resources.
2. Provide predictable performance and support SLAs for workload tenants in a multi-workload and multi-user environment.
3. Isolate and limit runaway queries or throttle IO resources for operations such as DBCC CHECKDB that can saturate the IO subsystem and negatively impact other workloads.
4. Add fine-grained resource tracking for resource usage chargebacks and provide predictable billing to the consumers of the server resources.

https://msdn.microsoft.com/en-us/library/bb933866.aspx