Hi Guys, 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. Thanks.
Check the DMVs for all your index statistics needs. select db_name(database_id), object_name(object_id, database_id), * FROM sys.dm_db_index_usage_stats should give you what you need in terms of current indexes. Similarly, there's a DMV `sys.dm_db_missing_index_details` that may be of interest for those indexes that don't already exist.
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.