SQL Optimizer for SAP ASE 3.9.1 - Installation Guide

Index Advisor Tutorial

The Index Advisor analyzes the syntax of a SQL statement and the database structure and then proposes new index candidates to help improve performance. It provides detailed information on the suggested indexes, such as, space requirements and selectivity. The index recommendations can be benchmarked to identify which index yields the greatest performance gain. It also enables you to create your own indexes for testing.

Create Index candidates

1. Click .

2. In the top pane under the SQL Editor tab, enter the SQL statement for which you want to analyze for index recommendations.

Note: To copy a SQL statement from other windows such as SQL Scanner or SQL Optimizer, click .

3. Click to see the current query plan and get a list of the indexes used in the current query plan. This index information displays in the Used Index tab of the bottom pane.

4. Click .

5. From the Select Tables to Provide Indexes window, select the tables on which you want recommendations for new indexes and specify the sampling size of each table to calculate selectivity. Click OK.

6. Once the advising process is completed, the Index Advising Details window displays detailing the index candidates. Click OK.

7. The index candidates are displayed on the bottom left pane on the tabs labeled Index1, Index2, …IndexN. The Used Index tab displays the DDL for the index(es) currently used by the original SQL statement. The corresponding query plan, abstract plan, SQL classification, trace on and Sort Resource information are displayed in the SQL Information pane at the bottom right.

8. You can add your own index candidates using . This option displays a GUI for you to create indexes for analysis.

Test index candidates

9. To get the actual run time information for the SQL statement under every index scenario, click . Select your benchmarking options in the Batch Run Criteria window. Click OK.

Important Note: This process may impact your database server. Specify the index creation options such as the segment where the index is going to be physically created, and then dropped, and the number of consumers.

10. The Batch Run window opens enabling you to view the results as each statement executes.

11. At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.

12. Click the Time tab in the Index Advisor window to see the results of the Batch Run.

13. To analyze the impact of every index alternative on the query plans of other SQL statements, click .

Related Topic

Index Advisor Overview