Share this page
Share this page E-mail this page Print this page RSS feeds
Home > Projects > AutoAdmin
AutoAdmin

Database management systems provide functionality that is central to developing business applications. Therefore, database management systems are increasingly being used as an important component in applications. Yet, the problem of tuning database management systems for achieving required performance is significant, and results in high total cost of ownership (TCO). The goal of our research in the AutoAdmin project is to make database systems self-tuning and self-administering. We achieve this by enabling databases to track the usage of their systems and to gracefully adapt to application requirements. Thus, instead of applications having to track and tune databases, databases actively auto-tunes itself to be responsive to application needs. Our research has led to novel self-tuning components being included in Microsoft SQL Server.

Current Status

Database management systems provide functionality that is central to developing business applications. Furthermore, as new cloud database services emerge, even more applications are beginning to use database systems. Yet, the problem of tuning database management systems for achieving the required performance is significant, and results in high cost of ownership. The goal of our research in the AutoAdmin project is to make database systems more self-tuning and self-administering. We approach this by enabling databases to track the usage of their systems and to gracefully adapt to application requirements. Thus, instead of the application having to track and tune the database, the database actively monitors, diagnoses and tunes itself to be responsive to application needs. Our research has led to novel self-tuning components being included in Microsoft SQL Server.

A key area of focus is physical database design. Physical database design is the problem of identifying the physical design structures such as indexes, materialized views and partitioning that are appropriate for the database and workload so as to optimize performance. Our research on this problem led to the development an Index Tuning Wizard that shipped as part of Microsoft SQL Server 7.0 (the first tool of its kind in the industry) and Microsoft SQL Server 2000, and the Database Engine Tuning Advisor (DTA) that shipped as part of Microsoft SQL Server 2005. This work was done in close collaboration with the Microsoft SQL Server product team. More details of the tool including its underlying algorithms are available in the VLDB 1997 paper "An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server " (this paper won the VLDB 10-Year Best Paper Award), the VLDB 2000 paper "Automated Selection of Materialized Views and Indexes for SQL Databases", the SIGMOD 2004 paper "Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design" and the VLDB 2004 paper "Database Tuning Advisor for Microsoft SQL Server 2005" (see Publications below).

An important requirement for a self-tuning database system is the ability to efficiently monitor the database and identify potential problems. As part of the AutoAdmin project we are investigating some of these issues. We developed a SQL Continuous Monitoring engine (SQLCM) in which DBAs can declaratively specify monitoring tasks that they wish to perform and have the engine execute these tasks efficiently. Another problem of interest is estimating the progress of long-running SQL queries which are common in decision support systems. We are also investigating how feedback from query execution can be used to improve the choice of execution plan of the query optimizer. Interestingly, there are many simple cases where existing mechanisms for obtaining execution feedback are limiting, i.e. they do not help improve the execution plan. We are therefore studying how to augment execution feedback mechanisms to overcome some of these limitations while keeping the monitoring overheads low.

In addition to physical database design tuning, we have also recently worked on other problems including flexible query hinting mechanisms, index defragmentation and application analysis and monitoring. A core DBMS engine component that is closely related our work in AutoAdmin is the query optimizer. Recently, we have started studying the issue of testing the correctness and quality of query optimizers, and quantitatively measuring their effectiveness for benchmark and real-world queries.

 

If you have questions about this project, please contact the Autoadmin research team (aart@microsoft.com).

 

Publications