Automatic plan choice validation using performance statistics

ABSTRACT

The query optimizer models data distribution and access paths to make the optimal plan choice for a given query. Sometimes the plan selection is poor because of modeling limitations, outdated statistics, incorrect optimization heuristics, etc. Hence it is useful to examine the plan choice made by the optimizer from an execution perspective and to impose validation rules on the actual execution plan to evaluate plan suitability. This approach treats the optimizer as a black box. The plan validation is based on the queries and data instead of the optimizer implementation details.

This paper describes {XPC}, a rule-based tool for Microsoft SQL Server [1] that helps users and developers achieve a better understanding of plan performance. We apply ideas similar to code profilers [2] to examine plan execution performance along with heuristic rules to the actual execution profile and probe for inefficiencies. This paper describes the overview and implementation of {XPC} and presents rules showing how {XPC} is useful in targeting plan performance issues.

REFERENCES

• 1: Microsoft SQL Server http://www.microsoft.com/sql
• 2: Glenn Ammons , Thomas Ball , James R. Larus, Exploiting hardware performance counters with flow and context sensitive profiling, ACM SIGPLAN Notices, v.32 n.5, p.85-96, May 1997
• 3: L. Giakoumakis and C. Galindo-Legaria. Testing SQL Server's Query Optimizer: Challenges, Techniques and Experiences. IEEE Bulletin of the Technical Committee on Data Engineering, 31, 1 (March 2008), 37--44
• 4: Michael Stillger , Guy M. Lohman , Volker Markl , Mokhtar Kandil, LEO - DB2's LEarning Optimizer, Proceedings of the 27th International Conference on Very Large Data Bases, p.19-28, September 11-14, 2001
• 5: S. Chaudhuri, V. Narasayya, and R. Ramamurthy. Diagnosing Estimation Errors in Page Counts Using Execution Feedback. In Proceedings of ICDE 2008
• 6: K. Kline and C. Fernandez, Microsoft T-SQL Performance Tuning http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart4_F.pdf
• 7: Extensible Markup Language (XML) http://www.w3.org/XML/
• 8: XML Showplans http://msdn2.microsoft.com/en-us/library/ms189298.aspx
• 9: W3C XML Query (XQuery) http://www.w3.org/XML/Query/
• 10: SQL Server 2005 Books Online, Transact-SQL Reference (Transact-SQL), September 2007 http://msdn2.microsoft.com/en-us/library/ms189826.aspx
• 11: SQL Server 2005 Books Online, Dynamic Management Views and Functions, September 2007 http://msdn2.microsoft.com/en-us/library/ms188754.aspx\
• 12: SQL Server 2005 Books Online, Using XML in SQL Server, September 2007 http://msdn.microsoft.com/en-us/library/ms190936.aspx
• 13: SQL Server 2008 Books Online, Query Processing Enhancements on Partitioned Tables and Indexes, February 2008, http://msdn.microsoft.com/en-us/library/ms345599(SQL.100).aspx
• 14: Microsoft SQL Server 9.0 Technical Articles, Partitioned Tables and Indexes in SQL Server 2005, http://msdn.microsoft.com/en-us/library/ms345146.aspx
• 15: SQL Server 2005 XML Showplan Schema, http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd.
• 16: Bart Duncan's SQL Weblog, Wide vs. Narrow Plans, http://blogs.msdn.com/bartd/archive/2006/07/27/680518.aspx.

INDEX TERMS

Primary Classification:
  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.4 Systems
          Subjects: Query processing

Additional Classification:
  C. Computer Systems Organization
  C.4 PERFORMANCE OF SYSTEMS
      Subjects: Reliability, availability, and serviceability; Performance attributes

  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.7 Database Administration
          Subjects: Data warehouse and repository

General Terms:
Design, Reliability

Keywords:
XML, data warehousing, optimizer, performance statistics, performance test, query plan