Do you have any script to scan the Tables and find out how many user seeks, user scan, user lookup on Each Individual Indexes?
That will be quite useful for me to find out which INDEX is unused or missing. Then I can drop the index which are unused.
asked Feb 15, 2011 at 08:37 AM in Default
Check out these DMVs if you want to build the query yourself:
But you can also use this scripts already out ther to check for missing indexes. Check out missing indexes script by Brent Ozar and also Glenn Berry's Diagnostic Information Queries and last two queries in this script
Check the DMVs for all your index statistics needs.
should give you what you need in terms of current indexes.
Similarly, there's a DMV
To add to the great answers so far - never forget that the statistics can be misleading.
As soon as you make changes to a system the index usage stats can start to warp. If you create a new index, drop an existing index, add statistics (manually or automatically), import lots of data, delete lots of data or any other massive change to the system, the indexes may start to be used differently.
It is also important to remember that the usage stats are reset when a database has been taken offline and brought back online, or when the server instance has been rebooted. Also the stats are kept around indefinitely as long as an index is there - this means that a system that has been running for months/years could have massively different usage patterns throughout that time and the stats won't tell you that.
Take the stats as a help to identify possible candidates for being dropped, never take them as set-in-stone recommendations.
answered Feb 15, 2011 at 10:50 AM