Run the Health Check Tool and select
Generate Usage Statistics. Index Usage Statistics
retrieves index usage statistics from the SQL server.
To see information about index usage:
- Select the
Generate Usage Statistics check box when you
Run the Health Check Tool.
You see the Index Usage Statistics in the report.
- Check for indexes with a high value for
user_updates and very low or zero values for
users_seeks and
user_scans. This means that this index is not
being used much, and you can get this index dropped from the system to improve
performance.
- Overall ideally you should see more seeks than scans. A high value
for
user_scans compared to
user_seeks could justify the addition of an
index to that table. Check the worst queries by reads or CPU results to find
out if a query is being run on the same table.