Share on Facebook Tweet on Twitter Share on LinkedIn Share by email
Database Tuning Advisor for Microsoft SQL Server 2005

Sanjay Agrawal, Surajit Chaudhuri, Lubor Kollar, Arun Marathe, Vivek Narasayya, and Manoj Syamala

Abstract

The Database Tuning Advisor (DTA) that is part of Microsoft SQL Server 2005 is an automated physical database design tool that significantly advances the state-of-the-art in several ways. First, DTA is capable to providing an integrated physical design recommendation for horizontal partitioning, indexes, and materialized views. Second, unlike today’s physical design tools that focus solely on performance, DTA also supports the capability for a database administrator (DBA) to specify manageability requirements while optimizing for performance. Third, DTA is able to scale to large databases and workloads using several novel techniques including: (a) workload compression (b) reduced statistics creation and (c) exploiting test server to reduce load on production server. Finally, DTA greatly enhances scriptability and customization through the use of a public XML schema for input and output. This paper provides an overview of DTA’s novel functionality, the rationale for its architecture, and demonstrates DTA’s quality and scalability on large customer workloads.

Details

Publication typeInproceedings
Published inVLDB
PublisherVery Large Data Bases Endowment Inc.
> Publications > Database Tuning Advisor for Microsoft SQL Server 2005