Phases of SQL Server 2012 Query Processing

The task of the SQL Server optimizer is to work out the most efficient execution plan for a given query. This task is done using the following four phases (see Figure 1).

Figure 1. Phases in processing a query

 

NOTE

This article refers to using the query optimizer for queries in SELECT statements. The query optimizer is also used for INSERT, UPDATE, and DELETE statements. The INSERT statement can contain a subquery, while the UPDATE and DELETE statements often have a WHERE clause that has to be processed.

1. Parsing   The query’s syntax is validated and the query is transformed in a tree. After that, the validation of all database objects referenced by the query is checked. (For instance, the existence of all columns referenced in the query is checked and their IDs are determined.) After the validation process, the final query tree is formed.
2. Query compilation   The query tree is compiled by the query optimizer.
3. Query optimization   The query optimizer takes as input the compiled query tree generated in the previous step and investigates several access strategies before it decides how to process the given query. To find the most efficient execution plan, the query optimizer first makes the query analysis, during which it searches for search arguments and join operations. The optimizer then selects which indices to use. Finally, if join operations exist, the optimizer selects the join order and chooses one of the join processing techniques. (These optimization phases are discussed in detail in the following section.)
4. Query execution   After the execution plan is generated, it is permanently stored and executed.

NOTE

For some statements, parsing and optimization can be avoided if the Database Engine knows that there is only one viable plan. (This process is called trivial plan optimization.) An example of a statement for which a trivial plan optimization can be used is the simple form of the INSERT statement.