Understanding the query optimization workflow
Now, it’s time to take a deeper look at how the SQL Database Engine creates optimized query execution plans. As referenced in Chapter 1, Understanding Query Processing, this is the second phase of query processing and for the most part, only Data Manipulation Language (DML) statements undergo query optimization. The query optimization process is defined by the following cumulative stages:
- Trivial Plan
- Exploration, which, in turn, includes three phases:
- Transaction Processing
- Quick Plan
- Full Optimization
In the Exploration stage, what differentiates between the several phases is the increasing sets of rules that apply to each one as the search for a good-enough query plan progresses. Users can learn about the optimization level of a given query execution plan by looking at the properties of that plan. The following sections include sample execution plans to illustrate the concepts covered here. Query execution plans...
