ASA SQL User's Guide
Working with Database Objects
Working with indexes
Understanding the Index Consultant
The first step of Index Consultant operation is to capture a workload of data manipulation and query operations. It does this by capturing information as an application or set of applications carries out operations on the database.
The workload consists of all SELECT, INSERT, UPDATE, and DELETE statements executed during the capture step. The context of these operations is not captured as part of the workload. For example, although queries executed as part of a stored procedure, trigger, or event handler are captured as part of a workload, the surrounding code is not captured. This leads to the following consequences:
Parameters are not captured.
Parameterized SQL statements are stored by substituting an appropriate literal value with the statement definition. For example, if a query with a parameter in the WHERE clause is executed 15 times with different values for the parameter, the Index Consultant captures 15 distinct queries, each with a separate constant instead of the parameter.
Queries against temporary tables cannot be analyzed.
Although queries against temporary tables are collected as part of the workload, the data present in the temporary table is not captured. Consequently, the query cannot be analyzed during the analysis step. The Index Consultant cannot provide recommendations for suitable indexes on temporary tables.
Connection state is not entirely captured.
The connection state includes the set of database options in effect when a query is executed. The OPTIMIZATION_GOAL option often has a major effect on the execution plan chosen by the optimizer, and is stored along with the query. Other options are not stored, however.
Server state is not captured.
The server state includes whether data is available in cache or whether it must be fetched from disk. It also includes the effect of concurrent operations.
| Caution Do not change the database schema during the workload capture step. Do not change the database schema between the capture step and the analysis step. Such changes invalidate the Index Consultant recommendations. |