Databases Reference
In-Depth Information
Search 1
The next phase, search 1, also called Quick Plan, uses additional transformation rules,
limited join reordering, and is appropriate for more complex queries. At the end of this
phase, SQL Server compares the cost of the cheapest plan to a second internal cost
threshold and, if the best plan is cheap enough, then the plan is selected. If the query is
still expensive and the system can run parallel queries (as described in the Parallelism
section in
Chapter 2, The Execution Engine
), this phase is executed again to find a good
parallel plan, but no plan is selected for execution after this point. At the end of this
phase, the costs of the best serial and parallel plans are compared, and the cheapest one is
used in the following phase, search 2, which we'll come to in just a moment.
As an example, the following query does not qualify for search 0 and will go directly to
search 1:
SELECT
*
FROM
HumanResources
.
Employee
WHERE
ManagerID
=
12
Listing 5-34.
Using the
sys.dm_exec_query_optimizer_info
DMV as shown in Listing 5-2, you
can display its optimization information seen in Table 5-12, which shows that only the
search 1 phase was executed.
Counter
Occurrence
Value
elapsed time
1
0.004
final cost
1
0.00657038
maximum DOP
1
0
optimizations
1
1
