SQL Optimizer for SAP ASE 3.9 - Release Notes

Index Impact Analyzer Tutorial

Index Impact Analyzer allows you to analyze the impact of new indexes on other SQL statements in your database.

Note: The indexes that are used in these scenarios will be physically created in order to retrieve the query plans for analysis and then dropped.

1. Click .

2. Click New Analysis. If this is your first time in the Index Impact Analyzer, the New Analysis wizard automatically opens.

Analyzer Page

3. In the New Analysis wizard, under the Analyzer page, select to check the effects of index creation by either Creating a new Analyzer ... or Continuing an existing Analyzer ....

4. Give the analysis a name and description for easy reference.

5. If you would like to create a folder for better organization of your analysis, click .

Select SQL Page

6. Choose the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by selecting .

8. Under the SQL Query Plans will be analyzed section, select the options for retrieving your query plan.

• Using existing query plan saved with the SQL
  This option uses the query plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.

• Obtaining a new query plan under the current connection
  This option retrieves the query plan with the current database logon. This current query plan is compared to the query plan that is retrieved after creating the new index(es).

Index Page

9. Name the Index Scenario and enter an optional description for easy reference.

10. Selecting the segment for the index scenario to be created in and the number of consumers.

11. If the Index Impact Analysis was called from the Index Advisor, the index scenarios are automatically display and you can choose the index scenarios to include for analysis.

12. To manually add indexes click .

• Give the index a name for easy reference.

• In the Index based on section of this page, specify in which database to create this index and specify the owner to use, and then specify the table that contains the column(s) to include in the index.

• The columns of the table display in the Table columns page and can be selected to use in the index scenario, under the Index columns page, by double clicking them or using the right arrow button.

• Specify the Index type by checking the option to have the index Unique and whether to create the index as Clustered or NonClustered.

13. Click OK to perform the Index Impact Analysis.

Note: The indexes are physically created in the database, and then the query plans for the selected SQL statements are retrieved. After the query plan retrieval, the indexes are dropped.

Review Index Impact Analysis Results

14. After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.

15. In the right pane for the scenarios, click Prognosis to see overall performance changes and SQL details.

Related Topic

Index Impact Analyzer Overview