Databases Reference
In-Depth Information
When a timeout is found, the Query Optimizer stops the optimization process and
returns the least expensive plan it has found so far. The best plan found so far could
be a plan found during the current optimization stage, but most likely it would be the
best plan found in the previous one. This obviously means that a timeout can happen
only on the search 1 and search 2 stages; no timeouts can occur on stage 0, as the Query
Optimizer needs to finish at least one optimization stage in order to find a good (or at
least viable) initial plan (the same applies when stage 1 is chosen as the first optimization
phase for a query). This timeout event is shown in the properties of a graphical plan as
Reason For Early Termination Of Statement Optimization
, as shown in Figure 5-11, or
in an XML plan as
StatementOptmEarlyAbortReason
. This event is also shown as the
timeout counter on the
sys.dm_exec_query_optimizer_info
DMV.
Figure 5-11:
Timeout example.
Finally, at the end of the optimization process, the chosen plan will be sent to the
execution engine to be run, and the results will be sent back to the client.
