An efficient cost-driven index selection tool for Microsoft SQL Server (1997)
Cached
Download Links
| Citations: | 153 - 18 self |
BibTeX
@INPROCEEDINGS{Chaudhuri97anefficient,
author = {Surajit Chaudhuri and Vivek Narasayya},
title = {An efficient cost-driven index selection tool for Microsoft SQL Server},
booktitle = {},
year = {1997},
pages = {146--155}
}
Share
 |
 |
 |
 |
||
OpenURL
Abstract
In this paper we describe novel techniques that make it possible to build an industrial-strength tool for automating the choice of indexes in the physical design of a SQL database. The tool takes as input a workload of SQL queries, and suggests a set of suitable indexes. We ensure that the indexes chosen are effective in reducing the cost of the workload by keeping the index selection tool and the query optimizer "in step". The number of index sets that must be evaluated to find the optimal configuration is very large. We reduce the complexity of this problem using three techniques. First, we remove a large number of spurious indexes from consideration by taking into account both query syntax and cost information. Second, we introduce optimizations that make it possible to cheaply evaluate the “goodness ” of an index set. Third, we describe an iterative approach to handle the complexity arising from multicolumn indexes. The tool has been implemented on Microsoft SQL Server 7.0. We performed extensive experiments over a range of workloads, including TPC-D. The results indicate that the tool is efficient and its choices are close to optimal. 1.
Keyphrases
microsoft sql server efficient cost-driven index selection tool index set step quot industrial-strength tool suitable index query syntax multicolumn index iterative approach spurious index novel technique query optimizer quot cost information physical design large number extensive experiment sql query optimal configuration sql database index selection tool
