zTuned: Automated SQL Tuning through Trial and (Sometimes) Error

SQL tuning—the attempt to improve a poorly-performing execution plan produced by the database query optimizer—is a critical aspect of database performance tuning. Ironically, as commercial databases strive to improve on the manageability front, SQL tuning is becoming more of a black art. It requires a high level of expertise in areas like (i) query optimization, run-time execution of query plan operators, configuration parameter settings, and other database internals; (ii) identification of missing indexes and other access structures; (iii) statistics maintained about the data; and (iv) characteristics of the underlying storage system. Since database systems, their workloads, and the data that they manage are not getting any simpler, database users and administrators often rely on intuition and trial and error for SQL tuning. This work takes the position that the trial and error (or, experiment-driven) process of SQL tuning can be automated by the database system itself in an efficient manner; freeing the user or administrator from this burden in most cases. We formalize the problem of tuning a poorly-performing execution plan. We then describe the design of a prototype system that automates SQL tuning using an experiment-driven approach. Experiments are conducted with almost zero impact on the user-facing production database. The nontrivial challenge we addressed was to plan the best set of experiments to conduct, so that a satisfactory (new) plan can be found quickly.