There are several reasons why an index was created but has never been used. sometime indexes are generated based on presumed access patters that are not used and or indexes got generated based on SQL generated by an application, when the applications gets a new service pack the TSQL could get updated to match new features and or users improvements made by the developers... usually the indexes are ignored as they are not that visible and not dropping them does not generate errors in the application.
It queries the system tables and looks for the last update and access of an index. one way to render the script useless is to rebuild all indexes on the server whether they are fragmented or not.
The script goes over the indexes in the current database and tells you when it was first used as well as when it was last used. one could then look at this, being a human,and reason whether an index was used or not.
The Script does not use parameters, unfortunately it's all hard coded.
To download the script click
here.
/*********************************************** Tables that do not have indexes that have been accessed ***********************************************/ SELECT OBJECT_NAME(stats.OBJECT_ID) AS tableName , max(stats.last_user_update) as last_access , min(stats.last_user_update) as first_access FROM sys.dm_db_index_usage_stats as stats join sys.objects as obj on obj.object_id =stats.OBJECT_ID WHERE database_id = DB_ID( ) and last_user_update is null and obj.is_ms_shipped=0 group by OBJECT_NAME(stats.OBJECT_ID)
The script only lists the indexes access first and last time in the current database, it does not generate drop statements.
Indexes, like tables consists of data, sometimes lots of data, usually between 30-70% of the disk space of a database is used used by indexes. Backups cost money, it costs money to make them and it costs money to keep them. It might sound silly but saving 20% of backup time and space accumulates to quite a volume of disk, network and backup resource. Having worked on databases where backing up the last day takes more than 24 hours one starts taking this serious. backups t
One more thing, unused indexes are stored in the same storage structure ("extends and data pages") as used indexes. lets rephrase this so that it becomes more obvious; the data of the index is stored useful or not. it needs to be read from disk,this takes time, network space for SAN solutions as well as memory on the server. Making sure you needed to do this could save your colleagues in storage, network and engineering time figuring out why some "unrelated" performance hit is slowing down a their system.