Databases Reference
In-Depth Information
Counter
Occurrence
Value
optimizations
1
1
tables
1
1
trivial plan
1
1
Table 5-11:
Optimization information for a trivial plan.
Table 5-11 shows that this was in fact a trivial plan optimization, using one table and a
maximum DOP of 0, and it also displays the elapsed time and final cost. This same query
was also used earlier to demonstrate the
sys.dm_exec_query_transformation_
stats
DMV, which illustrated the transformation rules used by the Query Optimizer in
this query, as shown previously in Listing 5-5.
The other possible value for the Optimization Level or StatementOptLevel properties
is FULL, which obviously means that the query did not qualify for a trivial plan, and
a full optimization was performed instead. Full optimization is used for more
complicated queries or queries using more complex features, which will require
comparisons of candidate plans' costs in order to guide decisions; this will be explained
in the next section.
Full optimization
If a query does not qualify for a trivial plan, SQL Server will run the cost-based optimiza-
tion process, which uses transformation rules to generate alternative plans, stores these
alternatives in the memo structure, and uses cost estimation to select the best plan. This
optimization process is executed in three stages, with different transformation rules being
applied at each stage.
