Index Usage Statistics
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.