skip to main content
10.1145/1385269.1385282acmconferencesArticle/Chapter ViewAbstractPublication PagesmodConference Proceedingsconference-collections
research-article

Automatic plan choice validation using performance statistics

Published: 13 June 2008 Publication History

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]
T. Ball and J. R. Larus. Optimally profiling and tracing programs. ACM SIGPLAN, 32, 5 (May 1997), 85--96
[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]
M. Stiller, G. Lohman, V. Markl, and M. Kandil. LEO-DB2's Learning Optimizer. In Proceedings of VLDB 2001, 19--28
[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.

Recommendations

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences
DBTest '08: Proceedings of the 1st international workshop on Testing database systems
June 2008
74 pages
ISBN:9781605582337
DOI:10.1145/1385269
Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Sponsors

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 13 June 2008

Permissions

Request permissions for this article.

Check for updates

Author Tags

  1. XML
  2. data warehousing
  3. optimizer
  4. performance statistics
  5. performance test
  6. query plan

Qualifiers

  • Research-article

Conference

SIGMOD/PODS '08
Sponsor:

Acceptance Rates

Overall Acceptance Rate 31 of 56 submissions, 55%

Contributors

Other Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

  • 0
    Total Citations
  • 235
    Total Downloads
  • Downloads (Last 12 months)3
  • Downloads (Last 6 weeks)1
Reflects downloads up to 19 Feb 2025

Other Metrics

Citations

View Options

Login options

View options

PDF

View or Download as a PDF file.

PDF

eReader

View online with eReader.

eReader

Figures

Tables

Media

Share

Share

Share this Publication link

Share on social media