KEMBAR78
Dive Into The Query Optimizer: Undocumented Insight Benjamin Nevarez Twitter: @benjaminnevarez | PDF | Program Optimization | Data
Scribd
Upload
189 views18 pages

Dive Into The Query Optimizer: Undocumented Insight Benjamin Nevarez Twitter: @benjaminnevarez

The document discusses the SQL Server query optimizer process. It is comprised of three main steps: 1) parsing and binding the SQL query, 2) simplifying the query tree, and 3) performing optimization in multiple phases. The optimization process explores different join orders and transformation rules to generate alternative execution plans that are stored and analyzed in a memo structure to determine the most efficient plan.

Uploaded by

abacusdotcom
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
189 views18 pages

Dive Into The Query Optimizer: Undocumented Insight Benjamin Nevarez Twitter: @benjaminnevarez

The document discusses the SQL Server query optimizer process. It is comprised of three main steps: 1) parsing and binding the SQL query, 2) simplifying the query tree, and 3) performing optimization in multiple phases. The optimization process explores different join orders and transformation rules to generate alternative execution plans that are stored and analyzed in a memo structure to determine the most efficient plan.

Uploaded by

abacusdotcom
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 18

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
& Optimization”

SQL Server 2012 Internals

Working with SQL Server for


15 years

PASS Summit/SQL Server


Connections speaker
Dive into the Query Optimizer

SQL is a high-level declarative language


Dive into the Query Optimizer

Query processing steps


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
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
Demo

Logical Trees
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


Demo

Simplification
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
Transformation Rules

Applying transformations does not necessarily reduce the cost of the


generated alternatives

Cost will be estimated later (only physical alternatives are costed)


Demo

Transformation Rules
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
The Memo

After Optimization
Demo

The Memo
Optimization Phases – Full Optimization

Search 0, Transaction Processing phase


Search 1, Quick Plan phase
Search 2, Full Optimization
Demo

The Optimization Phases


Dive into the Query Optimizer

Thank You!

You might also like