How to identify Obsolete/unused Indexes in SQL Server

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s