Although the Index Consultant provides a SQL script that you can run to implement its results, you may want to assess the results before implementing them. For example, you may want to rename the proposed index names generated during the analysis.
When assessing the results, consider the following:
Do the proposed indexes match your expectations? If you know the data in your database well, and you know the queries being run against the database, you may want 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 tracing session.
Are there strong correlations between the effects of proposed indexes? The index recommendations attempt to evaluate the relative benefit of each index separately. However, two indexes are of use only if both exist (a query can use both if they exist, and none if either is missing). You can study the Requests tab and inspect the query plans to see how the proposed indexes are being used.
Are you able to reorganize 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 need 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 database 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 database 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 want to rerun the analysis under different conditions.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|