AutoAdmin “what-if” index analysis utility

Abstract

As databases get widely deployed, it becomes increasingly important to reduce the overhead of database administration. An important aspect of data administration that critically influences performance is the ability to select indexes for a database. In order to decide the right indexes for a database, it is crucial for the database administrator (DBA) to be able to perform a quantitative analysis of the existing indexes. Furthermore, the DBA should have the ability to propose hypothetical (“what-if”) indexes and quantitatively analyze their impact on performance of the system. Such impact analysis may consist of analyzing workloads over the database, estimating changes in the cost of a workload, and studying index usage while taking into account projected changes in the sizes of the database tables. In this paper we describe a novel index analysis utility that we have prototyped for Microsoft SQL Server 7.0. We describe the interfaces exposed by this utility that can be leveraged by a variety of front-end tools and sketch important aspects of the user interfaces enabled by the utility. We also discuss the implementation techniques for efficiently supporting “what-if” indexes. Our framework can be extended to incorporate analysis of other aspects of physical database design.

References

1. 1 AutoAdmin Project, Database Group, Microsoft Research, http://www.research.microsoft.com/db.
2. 2 Choenni S., Blanken H. M., Chang T., "Index Selection in Relational Databases", Proceedings of 5th IEEE ICCI 1993.
3. 3 Chaudhuri, S., Motwani, R., Narasayya, V., "Random Sampling for Histogram Construction: How Much Is Enough?". Proceedings of ACM SIGMOD '98.
4. 4 Chaudhuri, S., Narasayya, V., "An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server. ". Proceedings of the 23rd VLDB Conference, Greece, 1997.
5. 5 Frank M., Omiecinski E., Navathe S., "Adaptive and Automative Index Selection in RDBMS", Proceedings of EDBT 92.
6. 6 Finkelstein S, Schkolnick M, Tiberio P."Physical Database Design for Relational Databases", ACM TODS, Mar 1988.
7. 7 Gupta H., Harinarayan V., Rajaramana A., Ullman J.D., "Index Selection for OLAP", Proceedings of ICDE97.
8. 8 Harinarayan V., Rajaramana A., Ullman J.D., "Implementing Data Cubes Efficiently", Proceedings of ACM SIGMOD 96.
9. 9 Labio W.J., Quass D., Adelberg B., "Physical Database Design for Data Warehouses", Proceedings of ICDE97.
10. 10 Olken F., "Random Sampling in Databases", Technical Report, 1993.
11. 11 Rozen S., Shasha D. "A Framework for Automating Physical Database Design", Proceedings of VLDB 1991.
12. 12 Stonebraker M., Hypothetical Data Bases as Views. Proceedings of ACM SIGMOD 1981.

Index Terms

• Applied computing

  • Computers in other domains

    • Digital libraries and archives

• Information systems

  • Data management systems

    • Database design and models

      • Physical data models

  • Information retrieval

    • Document representation

    • Search engine architectures and scalability

      • Search engine indexing

  • Information systems applications

    • Digital libraries and archives