1 / 24

XPC Automatic Plan Choice Validation Using Performance Statistics

XPC Automatic Plan Choice Validation Using Performance Statistics. Ashit Gosalia, Xin Zhang. Graphical Query Plan. DW Query 150+ Nodes. Outline. Motivation Scalability – an example issue Overall Design Rules Domain independent Domain specific - Known Issues for DW Future work

amandla
Download Presentation

XPC Automatic Plan Choice Validation Using Performance Statistics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. XPCAutomatic Plan Choice Validation Using Performance Statistics DBTest2008 Workshop, Vancouver, BC Ashit Gosalia, Xin Zhang

  2. Graphical Query Plan • DW Query • 150+ Nodes

  3. Outline • Motivation • Scalability – an example issue • Overall Design • Rules • Domain independent • Domain specific - Known Issues for DW • Future work • Questions DBTest2008 Workshop, Vancouver, BC

  4. Motivation • A rule-based tool for Microsoft SQL Server that helps users and developers achieve a better understanding of plan performance • Easy analysis of plans • Automation and integration with test infrastructure • Create a common vocabulary to describe plan problems • Training tool DBTest2008 Workshop, Vancouver, BC

  5. Scalability Issue • Symptom • Only 1-2 threads are used on 8-way machine.

  6. Scalability Issue Store Date Sales

  7. Scalability Issue Store Date Sales

  8. Scalability Issue - Impact Estimation • Based on Estimated Rows Per Thread • Scalability Issue Impact:* • 1 – (Total # of Rows/DOP) / (Total # of Max Rows Per Thread) *: It is a best case estimation, not accurate runtime estimation

  9. Scalability Issue Store Date Sales 87%

  10. Scalability Issue - Workload Results • Real world DW • 600GB of data • 100+ queries • 85% queries had scalability issues 10% of queries could perform at 8x DBTest2008 Workshop, Vancouver, BC 60% of queries could perform at 2x

  11. Overall Design Input Plan Analysis Engine Output Result Logs TEXT Logs TEXT Rule1 Logs TEXT Rule2 Rule1 Tables Tables Tables XML Internal Tables Tables XML DBTest2008 Workshop, Vancouver, BC Tables XML

  12. Domain Independent Rules • Inclusive \ Exclusive rows • Similar to code profilers • Help locate changes fast • Reprocessing ratio • Good plans drop rows fast • Skew measures • Help detect a known issue • Calculate impact • Join Order plan • Provide a different view of the plan DBTest2008 Workshop, Vancouver, BC

  13. Known Issues for DW Showplan XML Statistics XML • Missing Statistics • Missing Indexes • Large Join Analysis* • Dynamic Partition Elimination (SQL Server 2005 and 2008) • Multi-Consumer Spool • Common Sub-Expression Detection* • Serial Execution Showplan XML+ • Scalability Issue • Gatekeeper Row • Cardinality Estimation Error DBTest2008 Workshop, Vancouver, BC

  14. Future Work • Make this tool available to users • Extend and add rules • Add more data to SHOWPLAN • Create post analysis triage tools • Describe plan transformations concisely DBTest2008 Workshop, Vancouver, BC

  15. Questions? • Contact information • Xin Zhang - Shin.Zhang@microsoft.com • Ashit Gosalia – AshitG@microsoft.com DBTest2008 Workshop, Vancouver, BC

  16. Backup Slides DBTest2008 Workshop, Vancouver, BC

  17. New Rule: Implementation • Pattern matching by XQuery • Implemented in T-SQL stored procedures • Impact Analysis based on Estimation and Actual DBTest2008 Workshop, Vancouver, BC

  18. New Rule: Narrow Plans DBTest2008 Workshop, Vancouver, BC

  19. New Rule: Narrow Plans DBTest2008 Workshop, Vancouver, BC

  20. New Rule: Wide Plans DBTest2008 Workshop, Vancouver, BC

  21. New Rule: Wide Plans DBTest2008 Workshop, Vancouver, BC

  22. New Rule: Implementation DBTest2008 Workshop, Vancouver, BC

  23. Architecture: Overall Sp_FromDir Sp_FromFile Sp_SetupFileIssuesFound Sp_FromTrace Sp_<Issue> Sp_UpdateFilesIssuesFound Sp_StoreDetails Sp_StoreIssue DBTest2008 Workshop, Vancouver, BC Sp_GetErrorInfo Sp_GetErrorInfo

  24. Architecture: SP_<Issue> Sp_SetupIssuesFound Sp_KnownIssues ShowPlan XML Statistics XML Sp_MissingStats Sp_GateKeeperRows Sp_MissingIndexes Sp_Scalability Sp_BigJoin Sp_EstimatedRows DBTest2008 Workshop, Vancouver, BC Sp_DynamicPartitionElimination Sp_SerialExecution Sp_PTPDynamicPartitionElimination Sp_CSEDetection Sp_MultiConsumerSpool

More Related