What is an obsolete Index?

Obsolete or Unused Index is an index without any seeks/scans or lookups (applicable only for Clustered).So if the sum of all seeks+scans+lookups is zero, that is an indication the index is not being used for any read access. These indexes will be an overhead in your application as those needs to be updated and maintained unnecessarily for any change in the key columns values.

Here is a very simple script to identify obsolete indexes in your environment.

Note: The script validates the below points:

1. Indexes with 0 read access (seek+scan+lookups)
2. Only applicable to non clustered indexes
3. Those indexes should not be part of PRIMARY or UNIQUE constraints

;WITH INDEXUSAGESTATS AS
(
	SELECT  
			I.OBJECT_ID,  OBJECT_NAME(I.OBJECT_ID) AS TABLE_NAME,  I.INDEX_ID,
			SUM(I.USER_SEEKS) AS SEEKS,  SUM(I.USER_SCANS) AS SCANS,  SUM(I.USER_LOOKUPS) AS LOOKUPS, SUM(I.USER_UPDATES) AS WRITES
	FROM SYS.TABLES T
	INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS I  ON T.OBJECT_ID = I.OBJECT_ID
	GROUP BY
			I.OBJECT_ID, I.INDEX_ID
 )
SELECT
		'DROP INDEX ' + QUOTENAME(IUS.TABLE_NAME) + '.' + QUOTENAME(I.NAME) AS DROPINDEXSTATEMENT,
		IUS.TABLE_NAME AS TABLENAME, I.NAME AS INDEXNAME, I.TYPE_DESC AS INDEXTYPE,
		IUS.SEEKS AS SEEKS, IUS.SCANS AS SCANS, IUS.LOOKUPS AS LOOKUPS, IUS.WRITES  AS TOTALWRITES,
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS AS TOTALACCESSES
FROM INDEXUSAGESTATS AS IUS
INNER JOIN SYS.INDEXES I 	ON IUS.OBJECT_ID = I.OBJECT_ID  AND IUS.INDEX_ID = I.INDEX_ID
INNER JOIN SYS.TABLES B ON I.OBJECT_ID = B.OBJECT_ID
WHERE 
		/*OBSOLETE INDEX IS THOSE WITHOUT ANY SEEKS, SCANS and LOOKUPS*/
		IUS.SEEKS + IUS.SCANS + IUS.LOOKUPS = 0 
		/*QUERY EVALUATES ONLY NONCLUSTERED INDEXES THAT CAN NIETHER BE PRIMARY OR UNIQUE KEY*/
		AND I.TYPE_DESC = 'NONCLUSTERED'  AND I.IS_PRIMARY_KEY = 0  AND I.IS_UNIQUE = 0 
ORDER BY IUS.TABLE_NAME, I.NAME