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.
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.
The database executes each child operation before the parent operation. For some SQL statements, the database executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that the database retrieve all rows from the child operation before it executes the parent operation.
Execution Plan Window
The Execution Plan pane shows types of information for the original SQL or the SQL associated with the currently selected alternative.
Execution Plan Actions
Right-click in the Execution Plan window to select from the following actions:
| Action | Description |
| Copy | Copies the execution plan to the clipboard. |
| Save | Saves the execution plan as a JPG file. |
| Opens the print window so you can print the execution plan. | |
| Style |
Select option to display different plan details (operators, predicates, statistics). |
| View Plan | Allows you to change how the execution plan is displayed. |
| Step Description | Select this option to display a description of the step selected in the execution plan. |
| Animate Plan Steps | Highlights, one-by-one, the execution plan steps. |
| Plan Options | Opens the Execution Plan Options window so you can select which information is displayed in the execution plan and whether to display specific items in a column. |
| Help on RETURN | Displays the help text for the currently selected operation in the execution plan. |
| Help on Execution Plan | Opens online help for the Execution Plan window. |
Review Execution Plans
The execution plan can be displayed in different ways to help you get more detailed information.
To change how the execution plan is displayed
-
Right-click the plan and select View Plan.
-
Select one of the following display options:
As Tree Plan
As Plain Language Plan
As Graphic Plan
As MS Graphic Plan
Tips:
-
You can change the colors used to highlight different items for the current plan only by right-clicking the plan and selecting Plan Options. You can change colors for all execution plans in Options | General | Execution Plan. Execution Plan Options
-
You can use the toolbar to the right of the Execution Plan pane to change the details displayed.
Get Execution Plans
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
SQL Optimizer automatically retrieves the execution plan when you optimize or test run a SQL statement.
To get the execution plan
-
Select the Optimize SQL tab.
-
Enter a SQL statement in the Alternative Details pane.
- Click
.
Fill Missing Execution Plans
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To fill a missing execution plan
-
Select the Optimize SQL tab.
-
Enter a SQL statement in the Alternative Details pane.
- Click the arrow beside