1 / 13

Outline

Outline. SQL Server Optimizer Enumeration architecture Search space: flexibility/extensibility Cost and statistics Automatic Physical Tuning Database Tuning Advisor New Directions. Primary Index . Secondary Index. Running Example (TPC-H Database). Running Example (Query).

juana
Download Presentation

Outline

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. Outline • SQL Server Optimizer • Enumeration architecture • Search space: flexibility/extensibility • Cost and statistics • Automatic Physical Tuning • Database Tuning Advisor • New Directions

  2. Primary Index Secondary Index Running Example (TPC-H Database)

  3. Running Example (Query) Sample query: Obtain information about certain ordered line-items that are filtered by suppliers and parts. SELECTl_orderkey, l_linenumber, o_orderstatus FROM lineitem JOIN orders ON l_orderkey=o_orderkey WHERE l_suppkey<2000 AND l_partkey<2000

  4. SQL Server Query Optimizer • Based on Cascades Framework • Transformation-based, top-down approach • Optimization = Tasks + Memo( Programs = Algorithms + Data Structures ) • Fully cost-based • Flexible and Extensible • Search space easy to change • New operators and rules easy to add

  5. Expressions Groups The Memo • Search Space Memory • Compactly stores all explored alternatives (AND-OR graph) • Groups together equivalent operator trees and their plans • Provides memoization, duplicate detection, property and cost management, etc.

  6. Initialize MemoOptimize Root Group Optimization Tasks

  7. SQL Server Optimizer: Summary • Transformation-based, top-down approach No need for bottom-up interesting orders • Fully Cost-based No separation into phases (heuristic+cost) • Flexible and Extensible New operators, rules, and strategies are simple to add • Adaptive • Automatic statistics create and refresh • Automatic optimization levels • Physical Tuning

  8. Problem Statement Set of physical structures (i.e., indexes and views) that make similar workloads execute as fast as possible

  9. Challenges • Recommend a variety of physical structures. • Indexes, indexed views, partitions, XML indexes, etc. • Support space constraints, update queries. • Exceptionally large search space, especially for materialized views and partitions. • Strong interaction among access paths. • Merging needed: Optimal solution with suboptimal parts. • Cannot implement and test alternatives! • Industrial-strength quality (not trivial!)

  10. Design Principles • Workload-driven: Take into account database usage. • What-if API: Determine impact without actually materializing physical design. • Don’t second-guess the query optimizer! • An index is useful only if the query optimizer decides to use it. • Don’t use external estimator of goodness, but the optimizer-estimated cost. • Keep tool reasonably separated from today’s optimizer (extensibility).

  11. Workload Recommendation Database Tuning Advisor in Yukon Compress Workload … Query Optimizer Candidate Selection(per query) Database Server TuningClient What-ifAPI Metadata Merging Enumeration Yes - Create Hypothetical Index/View.- Optimize Query with respect to hypothetical configurations. Time? No

  12. Instrumenting the query optimizer. Search strategy based on relaxations. Workload Recommendation New Architecture … RequestsAPI Query Optimizer Request Identification Get Optimal Configuration(per query) Tuning Client What-ifAPI Database Server Relaxation Yes Metadata Time? No

  13. Related Bibliography • Surajit ChaudhuriAn Overview of Query Optimization in Relational Systems. PODS 1998: 34-43 • Goetz GraefeThe Cascades Framework for Query Optimization. IEEE Data Eng. Bull. 18(3): 19-29 (1995) • Surajit Chaudhuri, Vivek R. NarasayyaAn Efficient Cost-Driven Index Selection Tool for Microsoft SQL Server. VLDB 1997: 146-155 • Sanjay Agrawal, Surajit Chaudhuri, Vivek R. NarasayyaAutomated Selection of Materialized Views and Indexes in SQL Databases. VLDB 2000: 496-505 • Nicolas Bruno, Surajit ChaudhuriAutomatic Physical Database Tuning: A Relaxation-based Approach. SIGMOD 2005

More Related