SQL Optimizer for SQL Server 10.1.2 - User Guide

About Execution Plans

The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.

The following displays a sample execution plan in tree plan format:

Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.

SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.

The annotated execution plan includes the following information for each step:

• Execution order number

• Join syntax (annotated)

• Filter syntax (annotated)

• Object name

• Table access

• Index scan

• Cost

• Partition name

Actual Plan and Estimated Plan

In the Optimize SQL module, the Execution Plan window contains an Estimated Plan tab and an Actual Plan tab.

• The Estimated Plan is generated by the SHOWPLAN command.
• The Actual Plan is retrieved after executing SQL and includes real statistics such as Actual Rows, Actual Rebinds and Actual Rewinds that are not in an estimated plan.

Related Topics

Animate Execution Plans

Compare Plan Cost

Copy Execution Plans

Fill Missing Execution Plans