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 Performance and Tuning: Monitoring and Analyzing for Performance, and includes the following steps:
Add a 'loopback' server definition.
Run installmontables to install the monitoring tables.
Grant mon_role to all users who need to perform monitoring.
Set the monitoring configuration parameters. For more information, see Performance and Tuning: Monitoring and Analyzing for Performance.
You can use sp_monitorconfig to track whether number of open objects or number of open indexes are sufficiently configured.
Index selection-usage uses the following five 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 value is 3incremented, but
the UsedCount value does 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. For systems that are inadequately configured and have 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.
