SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) and will identify the unused index.

Indexes help us in covering and enhancing the performance of a large number of queries that try to retrieve data from the database table and SQL Server allows us to create up to 999 Non-clustered indexes and one Clustered indexes per each table and there is a Meta data table SYS.INDEXESwhich contains one row per each index in table or view.

 

SQL server stores the index usage information in the system table and it provides SYS.DM_DB_INDEX_USAGE_STATSview to access the index usage statistics information so with help of index usage information we can identify the un-used index and will remove them easily.

 

there are few important columns (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) in SYS.DM_DB_INDEX_USAGE_STATSthat help us to identify the bad or un-used index in table which are not being used in any query but in each update /insert operations , index data is getting updated.

 

·        USER_SEEKS - it stores the number of times the index is used to find a specific row.

·        USER_SCANS - it stores the number of times the leaf pages of the index are scanned.

·        USER_LOOKUPS - it stores the number of times a Clustered index is used by the Non-clustered index to fetch the full row .

·        USER_UPDATES - it stores the number of times the index data is modified

 

Here is a SQL query to pull the above information about the index.

 

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT 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()

      AND I.name='OrderOrderStatusDate_IDX'

 

Output:

 

As per output, the sql index “OrderOrderStatusDate_IDX” is not being used but index data is modified by 28046 times.