Automatic physical design tuning: workload as a sequence

The area of automatic selection of physical database design to optimize the performance of a relational database system based on a workload of SQL queries and updates has gained prominence in recent years. Major database vendors have released automated physical database design tools with the goal of reducing the total cost of ownership. An important assumption underlying these tools is that the workload is a set of SQL statements. In this paper, we show that being able to treat the workload as a sequence, i.e., exploiting the ordering of statements can significantly broaden the usage of such tools. We present scenarios where exploiting sequence information in the workload is crucial for performance tuning. We also propose techniques for addressing the technical challenges arising from treating the workload as a sequence. We evaluate the effectiveness of our techniques through experiments on Microsoft SQL Server.

[1]  Surajit Chaudhuri,et al.  Database tuning advisor for microsoft SQL server 2005: demo , 2005, SIGMOD '05.

[2]  Chun Zhang,et al.  Automating physical database design in a parallel database , 2002, SIGMOD '02.

[3]  Daniel C. Zilio,et al.  DB2 advisor: an optimizer smart enough to recommend its own indexes , 2000, Proceedings of 16th International Conference on Data Engineering (Cat. No.00CB37073).

[4]  Benoît Dageville,et al.  Automatic SQL Tuning in Oracle 10g , 2004, VLDB.

[5]  Arvola Chan,et al.  Index selection in a self-adaptive data base management system , 1976, SIGMOD '76.

[6]  Kai-Uwe Sattler,et al.  QUIET: Continuous Query-driven Index Tuning , 2003, VLDB.

[7]  Steve Rozen,et al.  Automating Physical Database Design: An Extensible Approach , 1993 .

[8]  Sam Lightstone,et al.  DB2 Design Advisor: Integrated Automatic Physical Database Design , 2004, VLDB.

[9]  Jeffrey D. Ullman,et al.  Index selection for OLAP , 1997, Proceedings 13th International Conference on Data Engineering.

[10]  Surajit Chaudhuri,et al.  Database Tuning Advisor for Microsoft SQL Server 2005 , 2004, VLDB.

[11]  Surajit Chaudhuri,et al.  SQLCM: a continuous monitoring framework for relational database engines , 2004, Proceedings. 20th International Conference on Data Engineering.

[12]  Surajit Chaudhuri,et al.  Automatic physical database tuning: a relaxation-based approach , 2005, SIGMOD '05.

[13]  R. K. Shyamasundar,et al.  Introduction to algorithms , 1996 .

[14]  Surajit Chaudhuri,et al.  An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server , 1997, VLDB.

[15]  Vivek R. Narasayya,et al.  Integrating vertical and horizontal partitioning into automated physical database design , 2004, SIGMOD '04.