Checking for unused indexes

Category : Scripts User Rating : 5 Stars      Views : 2.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
SQL Server provides a huge amount of performance related information in the Dynamic Management Views (DMVs). The DMV dm_db_index_usage_stats shows usage information about indexes. This can be very useful in identifying indexes that have been created but never used. Since maintaining table indexes does give rise to an overhead when data is updated or inserted, this can give performance gains as well as saving disk space.

A new row gets added to the dm_db_index_usage_stats DMV whenever an index is used for the first time, and the figures are subsequently updated each time an index is used. However it’s important to note that the figures do get reset when SQL Server is restarted.

The following script will quickly identify indexes that are used and those that are not used and is based on the DMV. It lists all indexes in the database and information about whether the index is used, and the number of updates, scans, seeks and lookups. For simplicity I’ve only included the most relevant columns from the DMV, but there are further columns which show usage by internal system queries and the time when the index was last accessed.

SELECT   T.name [Table Name],
         I.name [Index Name],
         -- Index is not used if it is not in the DMV
         -- OR if it is listed but there are no seeks, scans or lookups
             CASE WHEN (DMV.index_id IS NULL OR
                  (DMV.user_seeks = 0 AND DMV.user_scans = 0 AND DMV.user_lookups = 0 ))
                        THEN 'Index is NOT used'
         ELSE 'Index is used'
             END [Index Usage],
         I.Type_Desc [Index Type],
         ISNULL(DMV.user_updates,0) [User Updates],
         ISNULL(DMV.user_seeks,0)   [User Seeks],
         ISNULL(DMV.user_scans,0)   [User Scans],
         ISNULL(DMV.user_lookups,0) [User Lookups]
FROM  sys.objects AS T
JOIN  sys.indexes AS I
ON       T.object_id = I.object_id
LEFT JOIN  sys.dm_db_index_usage_stats AS DMV
ON       I.object_id = DMV.object_id  
AND      I.index_id = DMV.index_id
AND      DMV.database_id=DB_ID()
WHERE    T.type = 'U' -- eliminate system and internal tables
-- Only want clustered and non-clustered indexes (not heap)
AND      I.Type_Desc IN ('CLUSTERED', 'NONCLUSTERED')
ORDER BY T.name, I.name

There are other index related DMVs such as dm_db_index_operational_stats that give further information about indexes, as always there is more information in SQL Server books online.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Checking for unused indexes.aspx

Keywords

Scripts,indexes,unused,DMV


Comments
Post by OM on Tue 13 Aug 2013 19:36. Report Inappropriate Post

Thats great!!!! thank you

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered