1 / 18

Dive into the Query Optimizer

Dive into the Query Optimizer. Dive into the Query Optimizer: Undocumented Insight Benjamin Nevarez Blog: benjaminnevarez.com Twitter: @ BenjaminNevarez. About the Speaker Benjamin Nevarez. Author of “Inside the SQL Server Query Optimizer ” and “SQL Server 2014 Query Tuning

ona
Download Presentation

Dive into the Query Optimizer

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. Dive into the Query Optimizer Dive into the Query Optimizer: Undocumented Insight Benjamin Nevarez Blog: benjaminnevarez.com Twitter: @BenjaminNevarez

  2. About the SpeakerBenjamin Nevarez • Author of “Inside the SQL • Server Query Optimizer” and • “SQL Server 2014 Query Tuning • & Optimization” • SQL Server 2012 Internals • Working with SQL Server for • 15 years • PASS Summit/SQL Server • Connections speaker

  3. Dive into the Query Optimizer • SQL is a high-level declarative language

  4. Dive into the Query Optimizer • Query processing steps

  5. Dive into the Query Optimizer • The Optimization Process • Parsing / Binding (before optimization) • Simplification • Initial set of Join Orders • Trivial Plan • Optimization Phases • search 0 • search 1 • search 2

  6. Parsing / Binding • Parsing first makes sure that the T-SQL query has a valid syntax • Binding is mostly concerned with name resolution • Uses the query information to build a tree of relational operators

  7. Demo Logical Trees

  8. Simplification • Reduces the query tree into a simpler form in order to make the optimization process easier • Some of the simplifications include: • Redundant inner and outer joins may be removed. A typical example is the Foreign Key Join elimination • Filters in WHERE clauses are pushed down in the query tree in order to enable early data filtering (predicate pushdown) • Contradictions are detected and remove

  9. Demo Simplification

  10. Transformation Rules • Used to explore the search space • Exploration rules (logical transformation rules) • Generate logical equivalent alternatives • Commutativity • A join B – > B join A • Associativity • (A join B) join C – > A join (B join C) • Implementation rules (physical transformation rules) • Obtain physical alternatives • Join to Sort Merge Join • A join B – > A Merge Join B

  11. Transformation Rules • Applying transformations does not necessarily reduce the cost of the generated alternatives • Cost will be estimated later (only physical alternatives are costed)

  12. Demo Transformation Rules

  13. The Memo • Search data structure that is used to store the alternatives which are generated and analyzed by the Query Optimizer • A new memo structure is created for each optimization • The Query Optimizer copies the original query tree's logical expressions into the memo structure

  14. The Memo • After Optimization

  15. Demo The Memo

  16. Optimization Phases – Full Optimization • Search 0, Transaction Processing phase • Search 1, Quick Plan phase • Search 2, Full Optimization

  17. Demo The Optimization Phases

  18. Dive into the Query Optimizer Thank You!

More Related