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.
 
Search WWH ::




Custom Search