Improving Index Maintenance Using Decision Analysis

Abstract

One of the main activities of a Database Administrator (DBA) is to maintain an appropriate index configuration. However, adjustments in index configuration have become a very complex decision problem for DBAs. Making decisions concerning index maintenance often strains human cognitive capabilities. The DBA is faced with an intractable amount of factors that can impact the result of his/her decision. In order to address this issue a Decision Support System (DSS) can be used to support DBA decisions based on rational and systematic procedures. A DSS can model the chances of success of each DBA’s adjustment and the risk acceptance level of each decision maker using a probabilistic framework. This article presents a Decision Support Mechanism for Index Maintenance (DSIM), whose main feature is to provide a DSS to the problem of choosing the best point in time to maintain indexes for quasi-periodic time-consuming SQL queries. DSIM provides a risk acceptance configuration feature using a probabilistic framework. Experiments show that DSIM can be effectively deployed to support DBA’s decisions.