SQL Server Query Optimizer

The query optimizer uses cost-based optimization to create an execution plan with the least cost, but that also consumes the least amount of resources. The Query Optimizer will not evaluate all possible combinations of plans but will create a “Good Enough” plan based on available information such as statistics and the data distribution.

Trivial Plan Optimization

The Query Optimizer goes through several phases in the process of query optimization. First it must determine if the query has a trivial plan. Especially, a query that has only one plan to consider, such as, a SELECT statement where all the columns can be found in a single table or index. If a trivial plan is found, this saves the query optimizer from having to evaluate multiple plans, which reduces the workload of the optimizer. If a trivial plan is discovered then the optimization ends, and the query plan is created.

Non-Trivial Plan Optimization

When a trivial plan is unable to be generated, the query optimizer performs a simplification process to try to discover if unnecessary joins can be removed, operations can be rearranged, or commutative properties folded (Instead of 5 + 6, use 11 as a value). Additionally, before moving on to the next step, statistics, data distribution, and index metadata are added to the query tree.

Cost-based Optimization

Now the query optimizer will evaluate the query across three search phases to quickly identify the plan with the least cost.

• Phase 0 – Transactional Processing Phase: When online transaction processing (OTLP) queries are evaluated and there are three tables or less, the optimizer evaluates a limited number of join operators (merge, hash match, or loop) for potential plan candidates. If the estimated cost is less than 0.2 then the optimization ends, and the query plan is created.

• Phase 1a – Quick Serial Plan Optimization: Additional join orders and transformation rules are evaluated to create a cost-effective serial plan. If the plan cost is less than 1.0 then the optimization ends, and the query plan is created.

• Phase 1b – Quick Parallel Plan Optimization: At this point, if the cost of the plan is greater than 1.0, MAXDOP is set to a value greater than 1, and the plan cost is greater than the Cost Threshold for Parallelism; then the query optimizer repeats Phase 1 until it finds the most efficient parallel plan.

• Phase 2 – Compare Plans and Full Plan Optimization: At this point the serial plan that had a cost greater than 1.0 but less than the Cost Threshold for Parallelism is compared with the most efficient parallel plan. The optimizer will then take the cheaper of the two plans and consider if there are any additional ways of optimizing the execution plan. At this point, the optimization ends, and the query plan is created.