Full optimization

This is phase two and is used for complex queries, where the plan produced by phase one is still considered more expensive than the cost of searching for more alternative plans—the timeout defined previously. All internal transformation rules are available for use at this point but scoped to the search space defined in the preparation tasks, and parallelism is also considered.

The full optimization phase can go through a comprehensive set of optimization alternatives, which can make it time-consuming, especially if a query plan was not found in any preceding phase because phase two must produce a plan.

The timeout defined in the Quick plan section is the only condition that limits searching for a good-enough plan during full optimization. If a query plan was found before the timeout is hit, the execution plan will store information under the Reason For Early Termination Of Statement Optimization property about the outcome of the optimization stage, in this case showing the value Good Enough Plan Found value.

If the timeout is hit, the Query Optimizer will fall back on the lowest cost plan found so far. The execution plan will still store information under the Reason For Early Termination Of Statement Optimization property, in this case showing the value Time Out.

This property can be seen in the following example of a query executed in the AdventureWorks sample database :

SELECT pp.FirstName, pp.LastName, pa.AddressLine1, 
    pa.City, pa.PostalCode
FROM Person.Address AS pa
INNER JOIN Person.BusinessEntityAddress AS pbea 
    ON pa.AddressID = pbea.AddressID
INNER JOIN Person.Person AS pp 
    ON pbea.BusinessEntityID = pp.BusinessEntityID
WHERE pa.AddressID = 100;

See the following screenshot with the Reason For Early Termination Of Statement Optimization property:

The following graphic represents the query optimization workflow as described in this chapter:

For reference, the undocumented dynamic management view sys.dm_exec_query_optimizer_info exposes some interesting statistics gathered by Query Optimizer such as the number of optimizations that have been evaluated, as well as the drill-down of optimizations per stage, or the number of optimization-affecting hints have been used.