Inside the SQL Server Query Optimizer - part 7 cost bases optimization phases

Hi Guys,

Welcome back!

Are you ready for the last post of the series "Inside the SQL Server Query Optimizer"?
Let's go!

Introduction

You will surely remeber that in a previous post Inside the SQL Server Query Optimizer - part 4 Trivial Plan we talked about trivial plans.
Today we do another step forward.

Consider infact that rarely a Query is eligible for a trivial plan.
This is because as soon as we have an aggregate function or a union we can have multiple implementations and so a cost based decision is required.

Well, we have that if a Query is not eligible for a trivial plan then the optimizer proceeds with next steps called phases.

These Phases are:

• Transaction processing or Search 0
• Quick plan or Search 1
• Quick plan with parallelism 
• Full optimization or Search 2

Therefore we analyze each phase!

Transaction processing (Search 0)

The goal of this first phase is to find quickly an execution plan using only a limited set of rules.
The optimizer use search 0 phase only if there are at least 3 table, otherwise is skipped.
If an execution plan with a cost lower than 0.2 is found the optimization end here otherwise it will pass to the next phase.

Quick plan (Search 1)

In this step the optimizer uses more transformation rules.
At the end of this step if the cost is lower than 1.0 then the plan is selected, while if the costs is still high then the optimizer try to parallelize the Query.
At the end the cheapest plan between serial and parallel plan is sended to the next phase.

Full optimization (Search 2)

This is the last optimization phase and with the exception the of the timeout case a plan must be found here.
During this step all the potential rules are used.

An example

As usually i use our famous Query:

       
SELECT 
  MIN(T.ID) 
FROM ORDTES T
  JOIN ORDRIG R ON R.IDORDTES = T.ID
  JOIN ORDRIG R2 ON R2.IDORDTES = T.ID
WHERE 
  T.ID = 1
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8675)
       

In order to observe the Search 0 i use 3 tables.

And this is the result:

       
End of simplification, time: 0 net: 0 total: 0 net: 0
 
end exploration, tasks: 182 no total cost time: 0 net: 0 total: 0 net: 0
 
end search(0),  cost: 54.2702 tasks: 303 time: 0 net: 0 total: 0 net: 0.001
 
end exploration, tasks: 431 Cost = 54.2702 time: 0 net: 0 total: 0 net: 0.001
 
end search(1),  cost: 54.2702 tasks: 560 time: 0 net: 0 total: 0 net: 0.001
 
end exploration, tasks: 561 Cost = 54.2702 time: 0 net: 0 total: 0 net: 0.001
 
end exploration, tasks: 562 Cost = 54.2702 time: 0 net: 0 total: 0 net: 0.001
 
end exploration, tasks: 901 Cost = 54.2702 time: 0 net: 0 total: 0 net: 0.002
 
end exploration, tasks: 902 Cost = 54.2702 time: 0 net: 0 total: 0 net: 0.002
 
end search(1),  cost: 53.6271 tasks: 902 time: 0 net: 0 total: 0 net: 0.002
 
End of post optimization rewrite, time: 0 net: 0 total: 0 net: 0.002
 
End of query plan compilation, time: 0 net: 0 total: 0 net: 0.002
 
(1 riga interessata)
 
(1 riga interessata)   
 

Search 0 phase is used (we have 3 tables) but it's costs is greater than 0.2 so also Search 1 is used.

That's all for today.
With this post we are arrived to the end of this series.
I hope you enjoyed it and as usually tell me your doubt.
And don't forget to subscribe to this blog please! other interesting news are being prepared! 

See you soon,
Luca


Luca Biondi @ SQLServerPerformance blog 2020!   

 











Previous post: SQL server, Corona virus, Smartworking and the Index of the blog