Finding Unused Index

Introduction

To increase the performance of our Microsoft SQL Server we have to find the Unused index and Drop it.

How to find it

From MS SQL Server version 2005 introduce two DMV to see the usages status of the INDEX.

sys.dm_db_index_operational_stats

This DMV allows you to see insert, update and delete information for various aspects for an index.  Basically this shows how much effort was used in maintaining the index based on data changes.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [TABLE NAME],

       I.[NAME] AS [INDEX NAME],

       A.LEAF_INSERT_COUNT,

       A.LEAF_UPDATE_COUNT,

       A.LEAF_DELETE_COUNT

FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A

       INNER JOIN SYS.INDEXES AS I

         ON I.[OBJECT_ID] = A.[OBJECT_ID]

            AND I.INDEX_ID = A.INDEX_ID

WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1;

sys.dm_db_index_usage_stats

This DMV shows you how many times the index was used for user queries.

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [TABLE NAME],

       I.[NAME] AS [INDEX NAME],

       USER_SEEKS,

       USER_SCANS,

       USER_LOOKUPS,

       USER_UPDATES

FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S

       INNER JOIN SYS.INDEXES AS I

          ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID

WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

       AND S.database_id = DB_ID();

How to Decide

Based on the output of the two above query we have to decide.

If we see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index. 

Hope you like it.

Posted By: MR. JOYDEEP DAS