pootzko
7/18/2017 - 10:44 AM

http://mono.software/2017/07/13/missing-incorrectly-set-up-and-unused-indexes-and-their-impact-on-sql-server-performance/ Luckily, there ar

http://mono.software/2017/07/13/missing-incorrectly-set-up-and-unused-indexes-and-their-impact-on-sql-server-performance/

Luckily, there are many ways to find unused indexes, and I created a simple script that will get you started. It will help you identify the indexes that are not used, but server needed to create and update. Results are sorted by the number of writes (you can interpret it as a portion of server’s wasted resources). If you remove the dm_db_index_usage_stats.user_updates <> 0 predicate, the query will list all unused indexes, which now includes even indexes that server hasn’t done any work with.

indexes.sql
content_copyfile_download
SELECT
    objects.name AS TableName,
    indexes.name AS IndexName,
    dm_db_index_usage_stats.user_seeks AS UserSeek,
    dm_db_index_usage_stats.user_scans AS UserScans,
    dm_db_index_usage_stats.user_updates AS Writes
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0
    AND --remove to show all unused indexes
    dm_db_index_usage_stats.user_updates <> 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC