Databases Reference
In-Depth Information
•
Query optimization
- the logical tree is then used to run the query optimization
process, which roughly consists of the following two steps:
•
generation of possible execution plans
- using the logical tree, the Query
Optimizer devises a number of possible ways to execute the query, i.e. a number
of possible
execution plans
; an execution plan is, in essence, a set of physical
operations (an Index Seek, a Nested Loops Join, and so on), that can be performed
to produce the required result, as described by the logical tree
•
cost-assessment of each plan
- while the Query Optimizer does not generate every
possible execution plan, it assesses the resource and time cost of each plan it does
generate; the plan that the Query Optimizer deems to have the lowest cost of those
it has assessed is selected, and passed along to the execution engine.
•
Query execution, plan caching -
the query is executed by the execution engine,
according to the
selected
plan; the plan may be stored in memory, in the plan cache.
Parsing and binding are the first operations performed when a query is submitted to a
SQL Server instance. Parsing makes sure that the T-SQL query has a valid syntax, and
translates the SQL query into an initial tree representation: specifically, a tree of logical
operators representing the high-level steps required to execute the query in question.
Initially, these logical operators will be closely related to the original syntax of the query,
and will include such logical operations as "get data from the
Customer
table," "get data
from the
Contact
table," "perform an inner join," and so on. Different tree representa-
tions of the query will be used throughout the optimization process, and this logical tree
will receive different names until it is finally used to initialize the Memo structure, as will
be discussed later.
Binding is mostly concerned with name resolution. During the binding operation,
SQL Server makes sure that all the object names do exist, and associates every table and
column name on the parse tree with their corresponding object in the system catalog.
The output of this second process is called an algebrized tree, which is then sent to the
Query Optimizer.
