Query Optimization and Execution - Pro SQL Server Internals - page 452

SQL Server generates the trivial execution plan, which uses a Clustered Index Seek operator, as shown in
Figure 25-4 .

Figure 25-4. Trivial execution plan

Even though there are technically two different execution plan choices, Clustered Index Seek and Clustered Index
Scan , Query Optimizer does not consider a scan option, because it is clearly more expensive. Moreover, adding a
nonclustered index on Col1 or Col2 would introduce an additional, non-optimal execution plan choice. Nevertheless,
Query Optimizer is still able to detect it and generate a trivial execution plan instead.

■ you can check if an execution plan is trivial in the properties of the root operator or in xmL representation
of the plan.

Note

If a trivial plan was not found, SQL Server checks if any auto-updated statistics are outdated and triggers a
statistics update if needed. If the statistics need to be updated synchronously, which is the default option, Query
Optimizer waits until the statitistics update is finished. Otherwise, an optimization is done based on old, outdated
statistics, while statistics are updated asynchronously in another thread. After that, SQL Server starts a cost-based
optimization, which includes a few different stages. Each stage explores more rules and as a consequence, it can take
a longer time to execute.

Stage 0 is called Transaction Processing , and it is targeted at scenarios that represent an
OLTP workload with multiple (at least three) table joins, selecting a relatively small number
of rows using indexes. This stage usually uses nested loop joins; although in some cases it
may consider a hash join instead. Only a limited number of optimization rules are explored
during this stage.

Stage 1 is called Quick Plan , and it applies most of the optimization rules available in SQL
Server. It may be run twice, looking for serial and parallel execution plans if needed. Most
queries in SQL Server find the execution plan during this sta ge.

Stage 2 is called Full Optimization , and it performs the most comprehensive and, therefore,
longest running analysis, exploring all of the optimization rules available.