Ashit Gosalia
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.
- Microsoft SQL Server http://www.microsoft.com/sqlGoogle Scholar
- T. Ball and J. R. Larus. Optimally profiling and tracing programs. ACM SIGPLAN, 32, 5 (May 1997), 85--96 Google ScholarDigital Library
- 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--44Google Scholar
- M. Stiller, G. Lohman, V. Markl, and M. Kandil. LEO-DB2's Learning Optimizer. In Proceedings of VLDB 2001, 19--28 Google ScholarDigital Library
- S. Chaudhuri, V. Narasayya, and R. Ramamurthy. Diagnosing Estimation Errors in Page Counts Using Execution Feedback. In Proceedings of ICDE 2008 Google ScholarDigital Library
- K. Kline and C. Fernandez, Microsoft T-SQL Performance Tuning http://www.quest.com/Quest_Site_Assets/WhitePapers/WPD_MST-SQLPerformTuningPart4_F.pdfGoogle Scholar
- Extensible Markup Language (XML) http://www.w3.org/XML/Google Scholar
- XML Showplans http://msdn2.microsoft.com/en-us/library/ms189298.aspxGoogle Scholar
- W3C XML Query (XQuery) http://www.w3.org/XML/Query/Google Scholar
- SQL Server 2005 Books Online, Transact-SQL Reference (Transact-SQL), September 2007 http://msdn2.microsoft.com/en-us/library/ms189826.aspxGoogle Scholar
- SQL Server 2005 Books Online, Dynamic Management Views and Functions, September 2007 http://msdn2.microsoft.com/en-us/library/ms188754.aspx\Google Scholar
- SQL Server 2005 Books Online, Using XML in SQL Server, September 2007 http://msdn.microsoft.com/en-us/library/ms190936.aspxGoogle Scholar
- 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).aspxGoogle Scholar
- Microsoft SQL Server 9.0 Technical Articles, Partitioned Tables and Indexes in SQL Server 2005, http://msdn.microsoft.com/en-us/library/ms345146.aspxGoogle Scholar
- SQL Server 2005 XML Showplan Schema, http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd.Google Scholar
- Bart Duncan's SQL Weblog, Wide vs. Narrow Plans, http://blogs.msdn.com/bartd/archive/2006/07/27/680518.aspx.Google Scholar
Index Terms
- Automatic plan choice validation using performance statistics
Recommendations
Continuing plan quality optimisation
Finding high quality plans for large planning problems is hard. Although some current anytime planners are often able to improve plans quickly, they tend to reach a limit at which the plans produced are still very far from the best possible, but these ...
Computing Contingent Plan Graphs using Online Planning
In contingent planning under partial observability with sensing actions, agents actively use sensing to discover meaningful facts about the world. Recent successful approaches translate the partially observable contingent problem into a non-deterministic ...
Comments