It’s a boring job but sometimes a good DBA has to do it.
Applications change and you have to understand what become unuseful in your databases: we are talking about unused indexes. In any SQL server database indexes take up a lot of space and have to be updated every time an application runs an update on an indexed table. You have to rebuild and reorganize them… and you have to backup them, every day.
SQL Server gives you a good instrument to understand which indexes are really used. SYS.DM_DB_INDEX_USAGE_STATS is a dynamic management view used by SQL engine to collect information about this.
The first time a new index is used SQL server adds a new line to this table with many different counters. Those counters are used to collect data about the index every time it’s used. On every instance restart SQL reset index counters.
Querying this view is easy to understad which indexes are really used:
– Indexes not listed here are never used
– Indexes with blank counters are never used since the last instance restart
Those are a queries ready to make this kind of analisys. Remembar that not every application use alway ALL their indexes. Some indexes are used only when specific application functions are turned on. For this reason talk ALWAYS with application support guys before dropping anything.
--Indexes Never Used DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND I.INDEX_ID NOT IN ( SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbid) ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC GO
--Indexes never used since the last restart DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT u.* FROM [sys].[indexes] i JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID) LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id] AND u.[database_id] = @dbid WHERE o.[type] <> 'S' AND i.[type_desc] <> 'HEAP' AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 ORDER BY i.indexname asc