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


Implementing Index Consultant recommendations

The Index Consultant provides a SQL script that you can run to implement its recommendations. However, before doing so, you may want to assess the recommendations in the light of your own knowledge of your database. For example, the names of the proposed indexes are generated from the name of the analysis. You may want to rename them before creating the indexes in the database.

The following is a list of questions you should consider when assessing the recommendations:

  • 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 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, it is possible that 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.

See also