Databases Reference
In-Depth Information
The next step is the optimization process, which is basically the generation of candidate
execution plans and the selection of the best of these plans according to their cost. As
has already been mentioned, the SQL Server Query Optimizer uses a cost-estimation
model to estimate the cost of each of the candidate plans.
In essence, query optimization is the process of mapping the logical query operations
expressed in the original tree representation to physical operations, which can be carried
out by the execution engine. So, it's actually the functionality of the execution engine that
is being implemented in the execution plans being created by the Query Optimizer, that
is, the execution engine implements a certain number of different algorithms, and it
is from these algorithms that the Query Optimizer must choose, when formulating its
execution plans. It does this by translating the original logical operations into the physical
operations that the execution engine is capable of performing, and execution plans show
both the logical and physical operations. Some logical operations, such as a Sort, translate
to the same physical operation, whereas other logical operations map to several possible
physical operations. For example, a logical join can be mapped to a Nested Loops Join,
Merge Join, or Hash Join physical operator.
Thus, the end product of the query optimization process is an execution plan: a
tree consisting of a number of physical operators, which contain the algorithms
to be performed by the execution engine in order to obtain the desired results from
the database.
Generating candidate execution plans
As stated, the basic purpose of the Query Optimizer is to find an efficient execution plan
for your query. Even for relatively simple queries, there may be a large number of different
ways to access the data to produce the same end result. As such, the Query Optimizer
has to select the best possible plan from what may be a very large number of candidate
execution plans, and it's important that it makes a wise choice, as the time taken to return
the results to the user can vary wildly, depending on which plan is selected.
