When we
pass a T-SQL query to the SQL Server system, the first place it goes to is the relational
engine. As the T-SQL arrives, it passes through a process that checks that the T-SQL
is written correctly, that it's well formed. This process is query parsing.
If a query fails to parse correctly, for example, if we type SELETC instead of
SELECT, then parsing stops and SQL Server returns an error to the query source.
The output of the Parser process is a parse tree, or query tree (or it's
even called a sequence tree). The
parse tree represents the logical steps necessary to execute the requested
query.
If the
T-SQL string is a DML statement and it has parsed correctly, the parse tree is
passed to a process called the Algebrizer.
Each of these tasks is delegated to a separate component
within the query processor; the Query Optimizer devises the plan and then
passes it along to the Execution Engine, which will actually execute the
plan and get the results from the database.
Parsing and binding
The query is parsed and bound. Assuming the query is valid, the
output of this phase is a logical tree, with each node in the tree representing
a logical operation that the query must perform, such as reading a particular
table, or performing an inner join. This logical tree is then used to run the
query optimization process, which roughly consists of the following two steps;
Generate 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 seeks, a nested loop 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’s 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.