Now it's time to take a deeper look at how SQL Server creates optimized query execution plans. As mentioned in Chapter 2, 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 in itself:
Transaction processing
Quick plan
Full optimization
In the Exploration stage, what differentiates between the several phases is the increasing sets of rules applicable 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 will be discussed in more detail in Chapter 4, Exploring Query Execution Plans.