McBath's Oil Argument

McBath’s Oil Argument

The argument postulates that when you change the oil in your car, do you notice it going any faster? Probably not… but does your car know the difference? Definitely. If you don’t change the oil, the car’s engine will seize up.

Databases are the same. Any system can run with bad queries and a few users with brute force, but it will fail to scale. By tuning your system, you are changing your database’s “oil” and will get more users and more throughput from the same hardware.

For example, by repacking a database page from 80% full to 100% full you will go from 5 reads to 4 reads for the same amount of data. While you can’t even blink your eyes that fast, the aggregate effect is that a long running batch job that iterates on this small query will run 20% faster—and on a long payroll job, this can be significant and measured in hours saved.

Another example… It’s easy to find the few long running queries. I’m usually not scared of those. They are easy to locate with Profiler. What I am scared of is the query being run 10,000 times a day that is going very fast (sub-second), but doing more IO than it needs to. The aggregate of this common query can consume a lot of resources over time. If I can find those queries, then I can free up more disk and CPU resources for other needs.

I’ll use McBath’s Oil Argument fairly often when writing in the future.