The task of the 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 19-1).
NOTE This chapter 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, which might contain subqueries, too.
- 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.
- Query compilation The query tree is compiled by the query optimizer.
- 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 tasks are discussed in detail in the following section.)
- 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.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.