To Tweak or Not to Tweak? - 06 Apr 2000

To tweak or not to tweak, that is the question. For 'tis nobler to have a database that is fast than one that is tunable.

Shakespeare didn't work with many databases, but I'm sure he'd agree that speed is superior to tunability. Still, I've talked with a lot of people recently who position Oracle as better because it's "more tunable" than SQL Server. True, Oracle 8i has more than 400 tunable parameters while SQL Server 7.0 has fewer than 50—and that number drops in SQL Server 2000. The argument is that, because Oracle offers many more tuning parameters than SQL Server, you can make Oracle run more efficiently than SQL Server. However, this argument has a fatal foundational flaw: that "manually tunable" equals "optimal performance."

Database administrators and developers easily fall for this assumption. The engineer deep down inside us desperately wants to believe that we're smarter than the machine. We love to feel needed and powerful, like we do when our knowledgeable tweak produces an astonishing performance improvement. But it's time we got over ourselves. We're not that smart. And most of our tuning successes have come as a result of working with a database that couldn't do the job for us.

Remember when we optimized queries by listing tables and conditions in a certain order in the FROM and WHERE clauses? Wasn't that "tuning" the query? Today, no one seems to mind that cost-based optimizers find the best execution path for our queries. In fact, we'd laugh at a database product that didn't have a sophisticated cost-based optimizer. Why don't we expect the database engine to have the same level of intelligence?

One goal in rewriting SQL Server was to produce a product that "just works." To that end, Microsoft reduced or eliminated many administrative requirements in SQL Server 7.0 and incorporated into the database code the job of tuning the database for shifting workloads. Microsoft says the result is a database that has few manual tuning parameters but that features rich automatic-tuning functionality, letting the server constantly monitor and adjust memory allocation, optimizer plans, and locking to fit the latest application requirements. Competitive database vendors spin this to say SQL Server isn't tunable. Perhaps both statements are true.

Don't lose sight of the real goal. We don't want tunable databases; we want fast databases. I've seen plenty of DBAs "tune" their system to run incredibly slow. By the same token, a simple set of tuning parameters doesn't mean the database engine is simplistic or incapable of tuning itself. So, the next time a database salesperson says, "SQL Server isn't tunable," you can exclaim, "Great! That's one less thing I have to worry about."

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.