4.2 When is the query optimized?

When a query is submitted to SQL Server, various phases of processing occur. First of all, the query is parsed that is, it is syntax checked and converted into a parsed query tree that the standardization phase can understand. The standardization phase takes the parsed query tree and processes it to remove redundant syntax and to flatten subqueries. This phase essentially prepares the parsed query tree for query optimization. The output of this phase is a standardized query tree. This phase is sometimes known as normalization.

The query optimizer takes the standardized query tree and investigates a number of possible access strategies, finally eliminating all but the most efficient query execution plan. In order to formulate the most efficient query execution plan, the query optimizer must carry out a number of functions. These are query analysis, index selection, and join order selection.

Once the most efficient query execution plan is produced, the query optimizer must translate this into executable code that can execute under Windows operating systems. This code can then access the appropriate indexes and tables to produce the result set.

Figure 4.1 shows a simplified diagram of how query optimization takes place. In reality the process is much more complex but this gives us a basic idea.

Figure 4.1: Phases in query processing

How does the query optimizer work out the most efficient query execution plan? We will look at the way it does this now. We will see that it takes in...