ASA SQL User's Guide
  Working with Database Objects
    Working with indexes

Index Consultant overview

The selection of a proper set of indexes can make a big difference to the performance of your database. To help you in the task of selecting such a set of indexes, Adaptive Server Anywhere includes an Index Consultant. The Index Consultant guides you through the process of selecting indexes for a single query or for a set of database requests (called a workload). It creates many different sets of virtual indexes. For each set, the Index Consultant optimizes queries and other requests as if those virtual indexes were present. The Index Consultant then gathers the results of these explorations into a set of recommendations.

There are several stages in the Index Consultant's work. Understanding these stages helps you make the most of the tool.

1. Starting the Index Consultant.

   You can run the Index Consultant from either Interactive SQL or from Sybase Central. Access the Index Consultant from Interactive SQL to analyze indexes for a single query, and from Sybase Central to analyze indexes for a workload.

   For information on how to start the Index Consultant, see Starting the Index Consultant.

2. Capturing a workload.

   A workload is a set of queries or other data manipulation statements over which the Index Consultant tries to optimize performance. Depending on your goals, you may wish to make your workload a representative set of operations, or you may wish to identify a set of key bottleneck operations. The Index Consultant can capture one or more workloads, and can store them for later analysis.

   For more information, see Understanding workloads.

3. Analyzing the workload.

   The Index Consultant analyzes a workload or single query by generating candidate indexes and exploring their effect on performance. To explore the effect of different candidate indexes, the Index Consultant repeatedly re-optimizes the queries in the workload under different sets of indexes. It does not execute the queries.

   The Index Consultant can also store the results of multiple analyses on any workload, using different settings.

   When analyzing a workload, the Index Consultant presents you with a set of options:

   • Recommend clustered indexes    If this option is selected, the Index Consultant analyzes the effect of clustered indexes as well as unclustered indexes.

     Properly selected clustered indexes can provide significant performance improvements over unclustered indexes for some workloads, but you must reorganize the table (using the REORGANIZE TABLE statement) for them to be effective. In addition, the analysis takes longer if the effects of clustered indexes are considered.

     For more information about clustered indexes, see Using clustered indexes.

   • Keep existing secondary indexes    The Index Consultant can carry out its analysis by either maintaining the existing set of secondary indexes in the database, or by ignoring the existing secondary indexes. A secondary index is an index that is not a unique constraint or a primary or foreign key. Indexes that are present to enforce referential integrity constraints are always considered when selecting access plans.

   The analysis includes the following steps:

   • Generate candidate indexes    For each workload, the Index Consultant generates a set of candidate indexes. Creating a real index on a large table can be a time consuming operation, so the Index Consultant creates its candidates as virtual indexes. A virtual index cannot be used to actually execute queries, but the query optimizer can use virtual indexes to estimate the cost of execution plans as if such an index were available. Virtual indexes allow the Index Consultant to carry out "what-if" analysis without the expense of creating and managing real indexes. Virtual indexes have a limit of four columns.

   • Testing the benefits and costs of candidate indexes    The Index Consultant asks the Adaptive Server Anywhere query optimizer to estimate the cost of executing the queries in the workload, with and without different combinations of candidate indexes.

   • Generating recommendations    The Index Consultant assembles the results of the query costs and sorts the indexes by the total benefit they provide. It provides a SQL script, which you can run to carry out the recommendations or which you can save for your own review and analysis.

   For more information, see Understanding the analysis.

4. Implementing the recommendations.

   The Index Consultant provides a SQL script that you can run to implement its recommendations. Before doing so, you may wish to assess the recommendations in the light of your own knowledge of your database.

   Using the Index Consultant with Interactive SQL  The Index Consultant can only analyze one statement at at time from ISQL.

   For more information, see Assessing the recommendations, and Implementing the recommendations.