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:

1. Select the Generate Usage Statistics check box when you Run the Health Check Tool.
   You see the Index Usage Statistics in the report.

   

2. 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.
3. 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.