SQL Server Query Optimizer

The query optimizer uses cost-based optimization to create execution plans. Plans 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 passes through several phases during query optimization. First, it checks whether the query qualifies for a trivial plan; one that has only a single possible execution plan. For example, this occurs with a SELECT statement that retrieves all required columns from a single table or index. When the optimizer finds a trivial plan, it avoids evaluating multiple alternatives, which reduces its workload. Once it identifies a trivial plan, optimization stops and SQL Server generates the query plan.

Non-Trivial Plan Optimization

When the optimizer cannot generate a trivial plan, it moves onto the simplification phase. During this phase, the optimizer attempts to eliminate unnecessary joins, reorder operations, and fold commutative expressions (for example, replacing 5 + 6 with the constant value 11). Before proceeding to the next phase, it also updates the query tree with statistics, data distribution information, and index metadata.

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. The plan is checked if the plan cost is less than 1.0 or MAXDOP is set to 1. If true, 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 or MAXDOP is greater than 1. Then plan cost is checked to see if it is greater than the Cost Threshold for Parallelism. If the plan true 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.