Index selection

Index selection allows you to determine which indexes are actively being used and those that are rarely used.

This section assumes that the monitoring tables feature is already set up. See the Performance and Tuning Series: Monitoring Tables for information about installing and using the monitoring tables.

Index selection uses these columns of the monitoring access table, monOpenObjectActivity:

If a plan has already been compiled and cached, OptSelectCount is not incremented each time the plan is executed. However, UsedCount is incremented when a plan is executed. If no exec is on, OptSelectCount is incremented, but UsedCount is not.

Monitoring data is nonpersistent. That is, when you restart the server, the monitoring data is reset. Monitoring data is reported only for active objects. For example, monitoring data does not exist for objects that have not been opened, since there are no active object descriptors for such objects. If the system is inadequately configured and has reused object descriptors, monitoring data for these object descriptors is reinitialized and the data for the previous object is lost. When the old object is reopened, its monitoring data is reset.