Database Tuning Advisor for Microsoft SQL Server 2005

Abstract

This chapter provides an overview of Database Tuning Advisor's (DTA's) novel functionality, the rationale for its architecture, and demonstrates DTA's quality and scalability on large customer workloads. The DTA is part of Microsoft SQL Server 2005. It is an automated physical database design tool that significantly advances the state-of-the-art in several ways. First, the DTA is capable of 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, the DTA also supports the capability for a database administrator (DBA) to specify manageability requirements while optimizing for performance. Third, the DTA is able to scale to large databases and workloads using several novel techniques including: workload compression, reduced statistics creation, and exploiting test server to reduce load on production server. Finally, the DTA greatly enhances scriptability and customization through the use of a public XML schema for input and output.