ASA SQL User's Guide
Working with Database Objects
Working with indexes
Understanding the Index Consultant
Although the Index Consultant recommendations are based on an extensive search of available options, it is good practice to evaluate the recommendations before implementing them in a production database. For example, you may wish to carry out checks such as the following:
Do the proposed indexes match your own expectations? If you know the data in your database well, and you know the queries being run against the database, you may wish to check the usefulness of the proposed indexes against your own knowledge. Perhaps a proposed index only affects a single query that is run rarely, or perhaps it is on a small table and makes relatively little overall impact. Perhaps an index that the Index Consultant suggests should be dropped is used for some other task that was not included in your workload.
Are there strong correlations between the effects of proposed indexes? The index recommendations attempt to evaluate the relative benefit of each index separately. It may be the case, however, that two indexes are of use only if both exist (a query may use both if they exist, and none if either is missing). You may want to study the Requests tab and inspect the query plans to see how the proposed indexes are being used.
Are you able to re-organize a table when creating a clustered index? To take full advantage of a clustered index, you should reorganize the table on which it is created using the REORGANIZE TABLE statement. If the Index Consultant recommends many clustered indexes, you may have to unload and reload your database to get the full benefit. Unloading and reloading tables can be a time-consuming operation and can require large disk space resources. You may want to confirm that you have the time and resources you need to implement the recommendations.
Do the server and connection state during the analysis reflect a realistic state during product operation? The results of the analysis depend on the state of the server, including which data is in the cache. They also depend on the state of the connection, including some database option settings. As the analysis creates only virtual indexes, and does not execute requests, the state of the server is essentially static during the analysis (except for changes introduced by other connections). If the state does not represent the typical operation of your database, you may wish to rerun the analysis under different conditions.