6

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

Improve this question
3
  • 1
    What version/edition of SQL Server? You may want to look into Resource Governor if Enterprise...
    – FilamentUnities
    Aug 25, 2015 at 21:17
  • What is the MAXDOP setting on the instance ? I have seen cases where a rouge query will grind the server down if MAXDOP is set to default of 0 (use all threads) as opposed to a more sensible value.
    – Kin Shah
    Aug 25, 2015 at 21:17
  • we're using mssql 2012. I assume it's enterprise. I'm a dev so have local non-enterprise copy on my laptop.
    – user331465
    Aug 26, 2015 at 16:17

1 Answer 1

Reset to default
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.