The Exploration stage

If the Trivial Plan stage doesn't find a suitable plan, it's time to enter the cost-based optimization stage, known as Exploration, whose goal is to find a good-enough query execution plan based on the minimum estimated cost to access and join data. If this stage is used, the information on the optimization level is still stored in the execution plan under the same Optimization Level property, with a value of FULL.

The Exploration stage is where the CE comes into play. SQL Server loads statistics and performs some tasks in preparation for cost-based optimization.

These tasks are as follows:

• Simplification transforms some sub-queries into semi-joins, and even detects whether parts of the query can skip execution, for example avoiding empty tables or searching a table column for a NULL predicate when that table column has a trusted NOT NULL constraint.
• Normalization uses the query’s filter predicates and some heuristics to reorder join operations, and predicates are pushed-down to the algebrizer tree.

The cost-based optimization process itself is composed of three phases that we discuss in the next sections: Transaction Processing, Quick Plan, and Full Optimization.