How the SQL Server Query Optimizer Works

By:


In this article, we’ll briefly describe the inner workings of the query optimization process, which extends both before and after the query optimizer itself. The diagram in Figure 1 shows the major phases of the query processing, and each phase will be explained in more detail in the remaining sections of this chapter.

f0081-01

Figure 1 The query-processing process

Parsing and binding are the first operations performed when a query is submitted to a SQL Server instance. They produce a tree representation of the query, which is then sent to the query optimizer to perform the optimization process. At the beginning of this optimization process, this logical tree will be simplified, and the query optimizer will check whether the query qualifies for a trivial plan. If it does, a trivial execution plan is returned and the optimization process immediately ends. The parsing, binding, simplification, and trivial plan processes do not depend on the contents of the database (such as the data itself), but only on the database schema and query definition. Because of this, these processes also don’t use statistics, cost estimation, or cost-based decisions, all of which are only employed during the full optimization process.

If the query does not qualify for a trivial plan, the query optimizer will run the full optimization process, which is executed in up to three stages, and a plan may be produced at the end of any of these stages. In addition, to consider all of the information gathered in the previous phases, such as the query definition and database schema, the full optimization process will use statistics and cost estimation and then select the best execution plan (within the available time) based solely on that plan’s cost.

The query optimizer has several optimization phases designed to try to optimize queries as quickly and simply as possible, and to not use more expensive and sophisticated options unless absolutely necessary. These phases are called the simplification, trivial plan optimization, and full optimization stages. In the same way, the full optimization phase itself consists of three stages, simply called search 0, search 1, and search 2 (also called the transaction processing, quick plan, and full optimization phases, respectively). Plans can be produced in any of these phases except for the simplification one.

Leave a Reply