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. Ironi-cally, as commercial databases strive to improve on the man-ageability 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 inter-nals; (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 ad-ministrators often rely on trial and error for SQL tuning. In this paper, we take the position that the trial-and-error (or, experiment-driven) process of SQL tuning can be auto-mated by the database system in an efficient manner; freeing the user or administrator from this burden in most cases. A number of current approaches to SQL tuning indeed take an experiment-driven approach. We are prototyping a tool, called zTuned, that automates experiment-driven SQL tun-ing. This paper describes the design choices in zTuned to address three nontrivial issues: (i) how is the SQL tuning logic integrated with the regular query optimizer, (ii) how to plan the experiments to conduct so that a satisfactory (new) plan can be found quickly, and (iii) how to conduct exper-iments with minimal impact on the user-facing production workload. We conclude with a preliminary empirical eval-uation and outline promising new directions in automated SQL tuning. 1