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:
IndexID – unique identifier for the index.
OptSelectCount – reports the number of times that the corresponding object (such as a table or index) was used as the access method by the optimizer.
LastOptSelectDate – reports the last time OptSelectCount was incremented.
UsedCount – reports the number of times that the corresponding object (such as a table or index) was used as an access method when a query executed.
LastUsedDate – reports the last time UsedCount was incremented.
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.
