Welcome to SQL Optimizer
About SQL Optimizer
New in This Release
Connect to SQL Server
SQL Optimization Workflow
Support Bundle
Join the SQL Optimizer Community
Licensing
Optimize SQL
Create Optimize SQL Sessions
Open Optimize SQL Sessions
Rewrite SQL
Optimize Indexes
About Optimizing SQL (SQL Rewrite)
Select Connection
Optimize SQL Statements
Test Run SQL Alternatives
Auto Optimize SQL Statements
Test Run Settings
Test Run Special Settings
SQL Selector Workflow: SQL Rewrite
Execution Plans
Plan Control
About Execution Plans
Get Execution Plans
Fill Missing Estimated Plans
Copy Execution Plans
Animate Execution Plans
Compare Plan Cost
View Missing Indexes
Optimize SQL Used in Cursors
Generate Index Alternatives
About Generating Index Alternatives
Generate Index Alternatives
Test Run Index Alternatives
Troubleshoot Optimization Results
Retrieve Run Results
Create User Defined Alternatives
Execute Statements and View Results
Create Virtual Indexes
Set Bind Variables
Test for Scalability
Review Alternatives
Alternative SQL Text
View SQL Information (SQL Rewrite)
View Execution Statistics (SQL Rewrite)
View Schema Information (SQL Rewrite)
View Cursor Settings and Bind Variables (SQL Rewrite)
Show or Hide Table Attributes
Show or Hide Index Attributes
Show or Hide Column Attributes
Compare SQL Statements
Generate Optimize SQL Resolution Report
Troubleshoot Optimization Results
About Optimizing SQL (Plan Control)
Select Connection
Generate Execution Plan Alternatives
Test Run Execution Plan Alternatives
Auto Optimize Using Plan Guide
Test Run Settings
Test Run Special Settings
SQL Selector Workflow: Plan Control
Execution Plans
Use Temporary Tables
About Execution Plans
Get Execution Plans
Fill Missing Execution Plans
Fill Missing Estimated Plans
Copy Execution Plans
Animate Execution Plans
Compare Plan Cost
View Missing Indexes
Set Bind Variables
Retrieve Run Results
Review Plans
Original SQL Text
View SQL Information (Plan Control)
View Execution Statistics (Plan Control)
View Schema Information
View Bind Variables
Show or Hide Table Attributes
Show or Hide Index Attributes
Show or Hide Column Attributes
Compare Execution Plan Alternatives
Generate Optimize SQL Resolution Report
Deploy Plan Guides
About Optimize Indexes
Select Connection
Select a SQL Workload Source
Optimize Indexes from Scanned Code Workload
Optimize Indexes for Plan Cache Workload
Optimize Indexes for SQL Profiler Trace Files or Trace Tables Workload
Optimize Indexes for Spotlight Statistics Repository Workload
View Optimize Indexes Results
Create Index Scripts
Generate an Optimize Indexes Workload Report
Use Execution Plans
Find SQL
About Finding SQL
Find SQL - Homepage
Find SQL from Plan Cache
Scan SQL
Start finding SQL from Plan Cache
Select Connection
Search Results
Top SQL - Summary Chart
Top Batches - Summary Chart
Top Database Objects - Summary Chart
SQL by Classification
SQL Details Page
Batch Details Page
Database Object Details Page
SQL Classification Categories
Find SQL from SQL Trace
Start finding SQL from SQL Trace
Select Connection for SQL Trace
Search Results
Top SQL - Summary Chart
Top Batches - Summary Chart
Top Database Objects - Summary Chart
SQL by Classification
SQL Details Page
Batch Details Page
Database Object Details Page
SQL Classification Categories
Use Execution Plans
About Scanning SQL
About SQL Conversion
Scan SQL
Filter Database Objects
Scan Source Code with Temp Tables
Use Temporary Tables
Manage Groups
Manage Scan Jobs
Create Benchmark Factory Import Files
View SQL Classification
SQL Classification Categories
View SQL Information (SQL Scanner)
Use Execution Plans
Manage Plan Guides
About Execution Plans (Scan SQL)
Copy Execution Plans
Animate Execution Plans
Compare Plan Cost (Scan SQL)
Send to Optimize SQL
Generate Group Summary Reports
Generate Scanned Jobs Reports
About Managing Plan Guides
Select Connection
Create Manage Plan Guides Session
Manage Plan Guides
Filter Columns
Export, Import, and Migrate Plan Guides
Send to SQL Optimizer
Viewing Plan Guide Details
Configure Options
General
Tutorials
General Options
SQL Classification Options
Ansi-SQL Options
Execution Plan Options
Test for Scalability Options
Directory Setup Options
Database Setting Options
Appearance
Optimize SQL
Scan SQL
Manage Plan Guides
Tutorial: Optimize SQL (SQL Rewrite)
Tutorial: Optimize SQL (Plan Control)
Tutorial: SQL Scanner
Tutorial: Index Alternatives
Tutorial: User-Defined Temp Tables
Tutorial: Find SQL from Plan Cache
Tutorial: Find SQL from SQL Trace
Tutorial: Optimize Indexes
Tutorial: Manage Plan Guides
About Us
Get Execution Plans
SQL Optimizer automatically retrieves the execution plan when you optimize or execute a SQL statement. The Execution Plan window contains an Estimated Plan tab and an Actual Plan tab.
Note: When you generate virtual index alternatives, the execution plan is not retrieved for the SQL statement because SQL Optimizer does not physically create indexes on the database. See Fill Missing Estimated Plans for more information.
Estimated Plan
The Estimated Plan is generated by the SHOWPLAN command.
To get an estimated plan
- Enter a SQL statement in the Alternative Details pane.
-
Do one of the following:
- Click
.
- Select the Estimated Plan tab and click Get Estimated Plan.
Actual Plan
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.
To get an actual plan
| » | To retrieve an Actual Plan, do one of the following: |
Execute the selected SQL statement.
For the selected statement, select the Actual Plan tab and click Test Run - Current.
Tips for both Estimated and Actual Plans:
- To select which columns to display in the execution plan, right-click the plan and select Plan Options. The settings will apply to the current module only
- To change the font style and color, as well as the highlighting color, for the execution plans in all modules, select Options | General | Execution Plan See. Execution Plan Options for more information.
-
Use the Plan toolbar (right side of pane in tree view) to select from several options that display different plan details (operators, predicates, statistics).
Note: The Actual Plan tab displays and additional button at the top of the Plan toolbar. Click the Plan Comparison button to display a comparison of estimates and actual statistics.
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. |
| Plan Information | If you select this option, SQL Optimizer identifies whether an execution plan uses any of the following: cardinality feedback, dynamic sampling, SQL plan directives, or adaptive plan. If applicable, the information displays in the Execution Plan pane directly above the Execution Plan tab. |
| Animate Plan Steps | Highlights, one-by-one, the execution plan steps. |
| View Missing Indexes | Create a user-defined alternative. |
| Generate Indexes | Explore different index alternatives. |
| 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 SELECT | Displays definition of SELECT operator. |
| Help on Execution Plan | Opens online help for the Execution Plan window. |