Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Database Performance » Monitoring and improving performance » Application profiling » Index Consultant


Understanding Index Consultant recommendations

Before analyzing a tracing session, the Index Consultant asks you for the type of recommendations you want:

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

    Properly selected clustered indexes can provide significant performance improvements over uncluttered 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. See Using clustered indexes.

  • Keep existing secondary indexes   The Index Consultant can perform 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 tracing session, 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 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 perform "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 optimizer to estimate the cost of executing the queries in the tracing database, 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 implement the recommendations or which you can save for your own review and analysis.

The Index Consultant provides a set of tabs with the results of a given analysis. The results of an analysis can be saved for later review.

Summary tab

The Summary tab provides an overview of the analysis, including such information as the number of queries, the number of recommended indexes, the number of pages required for the recommended indexes, and the benefit that the recommended indexes are expected to yield. The benefit number is measured in internal units of cost.

Recommended Indexes tab

The Recommended Indexes tab contains data about each of the recommended indexes. Among the information provided is the following:

  • Clustered   Each table can have at most one clustered index. In some cases, a clustered index can provide significantly more benefit than an uncluttered index. See Using clustered indexes.

  • Pages   The estimated number of database pages required to hold the index if you choose to create it. See Table and page sizes.

  • Relative Benefit   A number from one to ten, indicating the estimated overall benefit of creating the specified index. A higher number indicates a greater estimated benefit.

    The relative benefit is computed using an internal algorithm, separately from the Total Cost Benefit column. There are several factors included in estimating the relative benefit that do not appear in the total cost benefit. For example, it can happen that the presence of one index dramatically affects the benefits associated with a second index. In this case, the relative benefit attempts to estimate the separate impact of each index.

    For more information, see Implementing Index Consultant recommendations.

  • Total Benefit   The cost decrease associated with the index, summed over all operations in the tracing session, measured in internal units of cost (the cost model). See How the optimizer works.

  • Update Cost   Adding an index introduces cost, both in additional storage space and in extra work required when data is modified. The Update Cost column is an estimate of the additional maintenance cost associated with an index. It is measured in internal units of cost.

  • Total Cost Benefit   The total benefit minus the update cost associated with the index.

Requests tab

The Requests tab provides a breakdown of the impact of the recommendations for individual requests within the tracing session. The information includes the estimated cost before and after applying the recommended indexes, as well as the virtual indexes used by the query. A button allows you to view the best execution plan found for the request.

Updates tab

The Updates tab provides a breakdown of the impact of the recommendations.

Unused Indexes tab

The Unused Indexes tab lists indexes that already exist in the database that were not used in the execution of any requests in the tracing session. Only secondary indexes are listed: that is, neither indexes on primary keys and foreign keys nor unique constraints are listed.

Log tab

The Log tab lists activities that have been completed for this analysis.

See also