ASA SQL User's Guide
Working with Database Objects
Working with indexes
Understanding the Index Consultant
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.
The Summary tab provides an overview of the workload and the analysis, including such information as the number of queries in the workload, 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.
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 unclustered index.
For more information about clustered indexes, see Using clustered indexes.
Pages The estimated number of database pages required to hold the index if you choose to create it.
For more information about database page sizes, see The Initialization utility.
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 Assessing the recommendations.
Total Benefit The cost decrease associated with the index, summed over all operations in the workload, measured in internal units of cost.
For more information on the cost model, see Optimizer estimates.
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.
The Requests tab provides a breakdown of the impact of the recommendations for individual requests within the workload. 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 enables you to view the best execution plan found for the request.
The Updates tab provides a breakdown of the impact of the recommendations.
The Unused Indexes tab lists indexes that already exist in the database that were not used in the execution of any requests in the workload. Only secondary indexes are listed: that is, neither indexes on primary keys and foreign keys nor unique constraints are listed.
The Log tab lists activities that have been completed for this analysis.