Understanding the query optimization workflow

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.