Generating and acting on recommendations for indexes for improving the performance of single SQL statements

The Index Advisor recommends indexes that you might create to improve the performance of the query. The Index Advisor can also give recommendations for modifying existing indexes.

Before you begin

About this task

The Index Advisor can give one of the following reasons for recommending an index:


To get recommendations for indexes:

  1. Return to the Run Single-Query Advisors and Tools page of the Invoke section in the workflow assistant. Click the Invoke tab on the left side of the workflow assistant. If the Run Single-Query Advisors and Tools page is not open, click Run Advisors and Analysis Tools on the left side of the workflow assistant to open the page.
  2. Optional: Modify values for the options for the Index Advisor. Click Set Advisor Options on the left side of the Invoke section. Then, click the Indexes tab to display the options that you can modify. For information about the options, click the Help icon. After you are finished, on the left side of the Invoke section click Run Advisors and Tools to return to the Run Single-Query Advisors and Analysis Tools page.
  3. If you are running the Index Advisor after running the RUNSTATS commands that the Statistics Advisor recommended, ensure that the Re-explain the query check box is selected. Now that the statistics for the SQL statement are accurate, the workflow assistant must update the EXPLAIN information for it.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Indexes check box. Then, click OK. After the Index Advisor runs, the Review Advisor Recommendations page of the Review section opens. The Summary section shows whether there are any recommendations for new indexes.
  6. In the Review Single-Query Advisor Recommendations page, double-click the Indexes row in the Summary table.
  7. Review the estimates of the performance improvement and disk space required if the recommended indexes are created.
  8. Review the list of candidate indexes. Here are descriptions of the columns of the table in which the indexes are listed.
    Indexes by Table
    Lists candidate indexes by the tables that the SQL statement references.
    Specifies whether the recommendation is to create, alter, or drop the index.
    The schema of the index.
    Object Name
    The name of the index.
    Key Columns
    The columns that are in the recommended index.
    Include Columns
    The include columns that are in the index. Include columns are appended to the set of index key columns. These columns are not used to enforce uniqueness. These columns might improve the performance of some queries through index only access. The columns must be distinct from the columns used to enforce uniqueness.
    Old Key Columns
    The columns that are in the current version of the index.
    Old INCLUDE Columns
    The include columns that are in the current version of the index.
    Indicates whether the recommendation is for a unique index.
    Estimated Disk Space
    The amount of disk space that the index will require.
  9. Click the Show DDL icon to see the DDL statements for creating the indexes that are listed in the table in the Candidate indexes section. You can save these statements to a file and run them outside of the workflow assistant.
    Attention: Before running DDL statements to modify existing indexes, you must check whether other SQL statements use those indexes in their access plans and would be affected by the changes.
  10. Optional: Test the candidate indexes that the Index Advisor recommends and any that you propose. For help with this feature, see Virtually testing recommended indexes and indexes that you propose.

What to do next

After running the DDL to create, modify, or drop indexes, run the Statistics Advisor for recommendations for collecting statistics on them. Before running the advisor, ensure that the Re-explain the query option is selected on the Run Single-Query Advisors and Tools page

Next, if you generated an access plan graph before running the Index Advisor, you can generate another access plan graph after creating the recommended indexes. Then, you can compare the two access plan graphs side by side in the Compare section of the workflow assistant.