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
This is a long-term project. In the short term, the focus is on automating 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.
We have developed an Index Tuning Wizard that shipped as part of Microsoft SQL Server 7.0 and Microsoft SQL Server 2000, and the Database Engine Tuning Advisor (DTA) that shipped as part of Microsoft SQL Server 2005. Given a workload consisting of a set of SQL statements, the Database Engine Tuning Advisor automatically recommends a set of indexes, materialized views and partitioning that are appropriate for that workload. A representative workload for the system can be gathered by logging activity on the server over a specified period of time. The user can specify constraints that must be met by the tool e.g., an upper bound on the storage space, or indexes that must be included. The tool also allows the user to perform a quantitative analysis of the impact of the proposed recommendation. For example, the database administrator can find the queries in the workload that are most affected by the proposed changes; or determine the fraction of queries where a particular index or materialized view is used. The tool also reports an expected improvement in performance if the proposed recommendations are accepted. If the user accepts the recommendations, the tool creates (and/or drops) indexes so that the recommended design is materialized. This work was done jointly with the SQL Server team. More details of the tool including its underlying algorithms are available in the VLDB'97 paper "An Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server ", the VLDB'00 paper "Automated Selection of Materialized Views and Indexes for SQL Databases", the SIGMOD'04 paper "Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design" and the VLDB'04 paper "Database Tuning Advisor for Microsoft SQL Server 2005". (see Publications below).
If you have questions about this project, please contact the Autoadmin research team (aart@microsoft.com).
- Vivek Narasayya and Manoj Syamala, Workload Driven Index Defragmentation, in ICDE, IEEE, 1 March 2010
- Arjun Dasgupta, Vivek Narasayya, and Manoj Syamala, A Static Analysis Framework for Database Applications, in International Conference on Data Engineering (ICDE), IEEE, April 2009
- Nicolas Bruno, Surajit Chaudhuri, and Ravishankar Ramamurthy, Power Hints for Query Optimization, in Proceedings of the International Conference on Data Engineering (ICDE), 2009
- Nicolas Bruno, Teaching an Old Elephant New Tricks, in Conference on Innovative Data Systems Research (CIDR), Very Large Data Bases Endowment Inc., 2009
- Surajit Chaudhuri, Vivek Narasayya, and Ravi Ramamurthy, A Pay-As-You-Go Framework for Query Execution Feedback, in VLDB, Very Large Data Bases Endowment Inc., August 2008
- Nicolas Bruno and Surajit Chaudhuri, Constrained Physical Design Tuning, in Proceedings of the International Conference on Very Large Databases (VLDB), 2008
- Surajit Chaudhuri, Vivek Narasayya, and Manoj Syamala, Bridging the Application and DBMS Profiling Divide for Database Application Developers, in VLDB, Very Large Data Bases Endowment Inc., September 2007
- Surajit Chaudhuri and Vivek Narasayya, Self-Tuning Database Systems: A Decade of Progress., in VLDB, Very Large Data Bases Endowment Inc., September 2007
- Nicolas Bruno and Surajit Chaudhuri, An Online Approach to Physical Design Tuning, in Proceedings of the International Conference on Data Engineering (ICDE), 2007
- Sanjay Agrawal, Eric Chu, and Vivek Narasayya, Automating Physical Database Design: Workload as a Sequence, in SIGMOD, Association for Computing Machinery, Inc., June 2006
- Nicolas Bruno, Surajit Chaudhuri, and Dilys Thomas, Generating Queries with Cardinality Constraints for DBMS Testing, in Transactions on Knowledge and Data Engineering, IEEE Computer Society, 2006
- Arnd Christian König and Shubha U. Nabar, Scalable Exploration of Physical Database Design, in 22nd International Conference on Data Engineering , IEEE Computer Society, 2006
- Sanjay Agrawal, Nicolas Bruno, Surajit Chaudhuri, and Vivek Narasayya, AutoAdmin: Self-Tuning Database Systems Technology, in Data Engineering Bulletin, IEEE Computer Society, 2006
- Zhimin Chen and Vivek Narasayya, Efficient Computation of Multiple Group-By Queries, in SIGMOD, Association for Computing Machinery, Inc., June 2005
- Nicolas Bruno and Surajit Chaudhuri, Flexible Database Generators, Very Large Data Bases Endowment Inc., 2005
- Sanjay Agrawal, Surajit Chaudhuri, Lubor Kollar, Arun Marathe, Vivek Narasayya, and Manoj Syamala, Database Tuning Advisor for Microsoft SQL Server 2005, in VLDB, Very Large Data Bases Endowment Inc., August 2004
- Surajit Chaudhuri, Vivek Narasayya, and Ravi Ramamurthy, Estimating Progress of Execution for SQL Queries, in SIGMOD, Association for Computing Machinery, Inc., June 2004
- Sanjay Agrawal, Vivek Narasayya, and Beverly Yang, Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design, in SIGMOD, Association for Computing Machinery, Inc., June 2004
- Surajit Chaudhuri, Arnd Christian König, and Vivek Narasayya, SQLCM: A Contiuous Monitoring Framework for Relational Database Engines, in 20th International Conference on Data Engineering, Institute of Electrical and Electronics Engineers, Inc., March 2004
- Nicolas Bruno and Surajit Chaudhuri, Conditional Selectivity for Statistics on Query Expressions, in SIGMOD, Association for Computing Machinery, Inc., 2004
- Surajit Chaudhuri, Prasanna Ganesan, and Vivek Narasayya, Primitives for Workload Summarization and Implications for SQL , in VLDB, Very Large Data Bases Endowment Inc., September 2003
- Sanjay Agrawal, Surajit Chaudhuri, Abhinandan Das, and Vivek Narasayya, Automating Layout of Relational Databases, in 19th International Conference on Data Engineering, IEEE Computer Society, 2003
- Surajit Chaudhuri, Vivek Narasayya, and Sunita Sarawagi, Efficient Evaluation of Queries with Mining Predicates, in 18th International Conference on Data Engineering, IEEE Computer Society, March 2002
- Nicolas Bruno, Surajit Chaudhuri, and Luis Gravano, STHoles: A Multidimensional Workload-Aware Histogram, no. MSR-TR-2001-36, March 2001
- Surajit Chaudhuri, Vivek Narasayya, and Sanjay Agrawal, Automated Selection of Materialized Views and Indexes for SQL Databases., in VLDB, Very Large Data Bases Endowment Inc., September 2000
