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