AutoAdmin "What-if" Index Analysis Utility
Surajit Chaudhuri (Microsoft Research)
Vivek Narasayya (Microsoft Research)
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.