Automated Selection of Materialized Views and Indexes in SQL Databases

TLDR

Selecting materialized views and indexes for SQL databases is complex, must be cost‑driven and workload‑aware, and prior work has focused on OLAP but not on an industry‑strength automated tool. The paper presents an end‑to‑end solution for selecting materialized views and indexes. The solution is implemented as part of a tuning wizard in Microsoft SQL Server 2000. Experimental evaluation demonstrates the effectiveness of the proposed techniques.

Abstract

Automatically selecting an appropriate set of materialized views and indexes for SQL databases is a non-trivial task. A judicious choice must be cost-driven and influenced by the workload experienced by the system. Although there has been work in materialized view selection in the context of multidimensional (OLAP) databases, no past work has looked at the problem of building an industry-strength tool for automated selection of materialized views and indexes for SQL workloads. In this paper, we present an end-to-end solution to the problem of selecting materialized views and indexes. We describe results of extensive experimental evaluation that demonstrate the effectiveness of our techniques. Our solution is implemented as part of a tuning wizard that ships with Microsoft SQL Server 2000.