Review Optimize Indexes Results
Step five. You can review the results or the progress of your search for new indexes in Optimize Indexes.
To View Optimize Indexes Search Results
- If you already have a session open, skip to 3. Otherwise, select Optimize Indexes.
-
Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
Note: You can view a summary of the results, or the progress of the search if it is ongoing, in the bottom pane of the Open SQL Workload window.
-
Select the Search Process tab to review details related to the search process. Each line on the graph indicates a new indexing recommendation.
Notes:
- Use your cursor to hover over a line on the graph to display a summary of the recommendation’s improvements (in a tooltip). Then click View details (in the tooltip) to open the Results page for the selected recommendation.
- Select All in the Display Range box, to display all index recommendations in the graph.
- Click List all results to display all index recommendations and their cost improvement values in a grid format. Click a recommendation in the list to open the results page for the selected recommendation.
-
Select the Results tab to view the final recommendation (or the last recommendation if the search process is ongoing).
Review the following for additional information:
Pane / Option / Button Description n Recommended Indexes Displays a list of the indexes for the recommendation.
Index Name—Click an Index name to see the index details, including a list of existing indexes which you can use to check for duplication.
Tip: Select (highlight) an index in the Recommended Indexes list to highlight the SQL statements in the Impacted SQL list that are impacted by that recommended index.
Results Summary (left side)—Displays a summary of workload improvement results.
Note: If the links at the bottom of the Results Summary pane are not in view, click and drag the pane's bottom edge down to resize.
(right side)—Displays the workload timeslots.
Use the cursor to hover over a timeslot to display a summary of the improvement to that timeslot’s SQL.
Note: Timeslots display only for workloads collected from snapshots.
View
Displays one of two different evaluations for the Workload:
- Plan Statistics view—Displays the estimated improvements based on changes in plan statistics and weighed by the number of executions for each SQL per timeslot.
- Projected Actual Statistics view—Displays the estimated improvements calculated using actual statistics and changes in plan statistics.
Publish Report
Click to view the Optimize Indexes Workload Report which you can customize and then export, print, or email. Send to Analyze Impact
Click to send the results to the Analyze Impact module where you can evaluate the impact of the new indexes on other SQL. Chart Options
Click this link to select which charts/statistics to display. Impacted SQL SQL Name—Click a SQL name to review the SQL statement and to compare the recommended execution plan to the original execution plan.
Tip: Select (highlight) a SQL statement in the Impacted SQL list to highlight the indexes in the Recommended Indexes list that contributed to the performance improvement of that statement.
Click to create a script to generate the new indexes recommended by Optimize Indexes.
Notes:
- To save the contents of a grid (list) as a file, right-click the grid and select Save As.
- An open database connection is not required when opening a completed session, reviewing results, or creating and publishing (printing) a report.
Tip: Use 
Generate an Optimize Indexes Workload Report
Send to Analyze Impact
You can send the virtual indexes created in Optimize Indexes to Analyze Impact to evaluate the effect of the new indexes on other SQL or database performance.
To send results to Analyze Impact
- Select Optimize Indexes.
- Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
- Open the Results page by clicking
.
-
On the Results page, in the Results Summary pane, click Send to Analyze Impact. A new Analyze Impact session opens.
-
The Indexing Change Details page is populated with the new indexes that you sent from the Optimize Indexes session. Use this page to modify the index or indexes. See Analyze the Impact of Additional Indexes for more information.
- When you have finished modifying the indexes, click
to start collecting the SQL to evaluate. See Collect SQL and Run an Impact Analysis.
Tip: Use
Create Index Scripts
After Optimize Indexes finds indexes that optimize a SQL workload, you can create the scripts to use to generate the indexes in your database environment.
To create index scripts for new indexes
- Select Optimize Indexes.
- Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
- Open the Results page by clicking
-
Click
to generate the index scripts.
The Index Script page opens where you can modify scripts and index names. Review the following for additional information:
Pane / Option Description Naming Convention Prefix, Name, Suffix
Create a naming convention for SQL Optimizer to use to give the index a name in the script. You can include a prefix and a suffix. The naming convention is applied to all the indexes in this session. n Recommended Indexes Modify Tablespace
Click to select a tablespace for the selected index or indexes. Modify Schema
Click to select a schema for the selected index or indexes. Generated Index Scripts SQL Optimizer automatically creates the index generating scripts. You can use this text editor to modify the scripts.
Tip: Use the right-click menu or buttons at the bottom of the window to copy or to print the scripts.
Tip: Use
Generate Report
After SQL Optimizer finds new indexes to optimize a SQL workload, you can generate a report that includes the workload summary, the recommended indexes, the index scripts, and the impacted SQL statements.
To generate an Optimize Indexes Workload Report
- Select Optimize Indexes.
- Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
- Open the Results page by clicking
-
On the Results page, in the Results Summary pane, click Publish Report. SQL Optimizer generates the report and opens it in the Report window.
-
Use the report window to customize the layout and the information displayed in the report, and to select publishing options. Use the following Report toolbar buttons.
Toolbar Button Description Current Settings. Click to specify the information to include in your report. Page Setup. Click to modify margins and select page orientation. Scale. Click to adjust the scale of the printed output. Watermark. Click to add a watermark to the report.
- To upload an image to use as the watermark, select the Picture Watermark tab.
Color. Click to change the report background color. Export Document. Click to save the report to file using the default format. Click the arrow to select from several file format options (or to change the default setting).
You can select from several different file formats, including PDF, HTML, and Excel.
Send via Email. Click to save the report to file using the default format and to also attached the file to an email. Click the arrow to select from several file format options (or to change the default setting).
To export the report as a PDF file
-
On the report page, select File | Export Document | PDF File, or click
and select PDF from the list.
Tip: You can select from several different file formats for the exported (published) report, including HTML and Excel.
- In the PDF Export Options dialog, configure the PDF options and click OK.
- Enter a file name and select a location for your report.
Tip: Use