Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Every once in a while, I need to run some very expensive query on a production server. Think spaghetti joins. This is likely to affect the server operation, and I don't want that.

What ways are there to limit the processor / ram requirements of a specific query, basically trading speed and freshness for reliability?

share|improve this question

You can set the session to allow uncommitted reads like so:


which will reduce the locking abnd concurrency overhead of your queries (but may make the results inconsistent as you will sometimes see "dirty reads" and similar). For more detail see

This will not limit the CPU/RAM/IO footprint of the query itself significantly though.

A common solution to this sort of thing (which may be overkill depending on your circumstances) is to maintain a slave replica on another server (or the same server but on different physical disks if I/O contention is your only/main concern) and run hefty reporting queries from there. Similarly if your reports are not real-time you could restore your backups to another place instead of the hassle of maintaining replication, so if you have daily backups you restore those elsewhere after they are made so your reporting queries don't impact the live DB at all but your reports are only correct as of the last backup. Neither of these methods works if your heavy SQL needs to perform updates or inserts though.

Reusing your backups in this way can be a useful way to verify that your backup arrangement is working: if your automatic restore to the reporting copy fails then that may be an early warning that your backup regime needs work. It is surprising how many people religiously schedule backups but never test them until they need to restore one in an emergency (hence there are quite a few panicked "how do I restore from a corrupt backup?" questions out there in various forums!).

share|improve this answer
I'm doing the "run on a separate instance from a backup" thing already. Thanks. – Gabriel R. Sep 13 '13 at 10:29

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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