asked
Index Seek
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 out these DMVs if you want to build the query yourself: `sys.dm_db_index_usage_stats`, `sys.dm_db_missing_index_group_stats`, `sys.dm_db_missing_index_details`, `sys.dm_db_index_physical_stats` along with `sys.indexes`.` But you can also use this scripts already out ther to check for missing indexes. Check out [missing indexes script by Brent Ozar][1] and also Glenn Berry's [Diagnostic Information Queries][2] and [last two queries in this script][3] [1]:
http://sqlserverpedia.com/wiki/Find_Missing_Indexes [2]:
http://sqlserverperformance.wordpress.com/2011/02/02/february-2011-version-of-sql-server-2005-and-2008-diagnostic-information-queries/ [3]:
http://sqlserverperformance.wordpress.com/2011/02/04/five-dmv-queries-that-will-make-you-a-superhero-in-2011/
@Leo, Basically the idea of query no 4 is to identify bad non clustered indexes by comparing the number of writes with reads. If the write is much higher and the reads are very small or zero the index might be hurting your write/update performance and you might be better of dropping it. Query no 5 will help you identify missing indexed and you can see their impact if added. (Becareful though on adding all suggested indexes) Glenn explained both these queries in great detail in this post
http://sqlserverperformance.wordpress.com/2011/01/06/sql-server-index-tuning-for-mere-mortals/ .
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.
Also, be careful about dropping unique indexes as the Query Optimizer can use their existence to eliminate unnecessary operations. For example, if you do SELECT DISTINCT ColumnA FROM TableB and there is a unique index on ColumnA, the Optimizer will ignore the DISTINCT bit to make a more efficient plan. This won't show up in the DMVs because no scans or seeks are needed - the index's metadata is enough.
