Database Reference
In-Depth Information
Chapter 25
Query Optimization and Execution
SQL Server Query Processor is perhaps the least visible and least well-known part of SQL Server. It does not expose
a large set of the public features, and it allows very limited control in a documented and supported way. It accepts a
query as input, compiles and optimizes it generating the execution plan, and executes it.
This chapter discusses the Query Life Cycle, and it provides a high-level overview of the Query Optimization
process. It explains how SQL Server executes queries, discusses several commonly used operators, and, addresses
query and table hints that you can use to fine-tune some aspects of query optimization.
Query Life Cycle
Every query submitted to SQL Server goes through a process of compilation and execution. That process consists of
the steps shown in Figure
25-1
.
Figure 25-1.
Query Life Cycle
When SQL Server receives a query, it goes through the
parsing
stage. SQL Server compiles and validates the
query's syntax and transforms it into a structure called a
logical query tree
. That tree consists of various
logical
relational algebraic operators, such as inner and outer joins, aggregations, and others.
In the next step, called
binding
, SQL Server binds logical tree nodes to the actual database objects, converting the
logical tree to a
bound tree
. It validates that all objects referenced in the query are valid; that they exist in the database,
and that all columns are correct. Finally, SQL Server loads various metadata properties associated with tables and
columns, for example
CHECK
and
NOT NULL
constraints.
Query Optimizer uses the bound tree as input during the
optimization
stage, when the actual
execution plan
is generated. The execution plan is also a tree-like structure, which is comprised of
physical
operators and is used
by SQL Server to execute a query. Physical operators perform the actual work during query execution, and they are
different from logical operators. For example, a logical inner join can be transformed to one of three physical joins,
including a nested loop, merge, or hash join.