Tag Archives: Finding Unused Indexes

SQL Queries That Identify Unused Indexes – Finders Keepers!

I’ve always found indexing, and performance tuning in general, extremely interesting.  Trying to understand how it all works…and works so well…has been a great part of the journey.  That whole “Is it an art or is it a science” thing…  

Well…I think I may have had some kind spiritual awakening or something here recently because part of the mystery of tuning and maintaining indexes has been revealed to me.

Okay…maybe they weren’t that kind of a revelations…but the the two queries below and the paths they led me down were very enlightening.  I am re-publishing them here, not to take credit for them, but because they, or some variation of them, will remain part of my toolkit.

This first query was posted by Jason Massie – a.k.a. StatisticsIO



, i.index_id  

, reads=user_seeks + user_scans + user_lookups  

, writes =  user_updates  

, p.rows

FROM sys.dm_db_index_usage_stats s JOIN sys.indexes

ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID  

JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID


AND s.database_id = DB_ID()  

AND i.type_desc = ‘nonclustered’

AND i.is_primary_key = 0

AND i.is_unique_constraint = 0

AND p.rows > 10000

ORDER BY reads, rows DESC


Sums all the reads that have occurred, on an index, and then limits the result set to non-clustered indexes on tables with row counts greater than 10000.  You can quickly see which tables are heavily written to…but not read from.



The query below was posted by Michell Ufford – a.k.a SQLFool


    ,@dbName VARCHAR(100);


SELECT @dbid = DB_ID()

    ,@dbName = DB_NAME();


WITH partitionCTE (OBJECT_ID, index_id, row_count, partition_count)




        , index_id

        , SUM([ROWS]) AS ‘row_count’

        , COUNT(partition_id) AS ‘partition_count’

    FROM sys.partitions


        , index_id




        , i.name

        , CASE

            WHEN i.is_unique = 1

                THEN ‘UNIQUE ‘


          END + i.type_desc AS ‘indexType’






        , CASE WHEN partition_count > 1 THEN ‘yes’

            ELSE ‘no’ END AS ‘partitioned?’

        , CASE

            WHEN i.type = 2 And i.is_unique = 0

                THEN ‘Drop Index ‘ + i.name

                    + ‘ On ‘ +@dbName

                    + ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID]) + ‘;’

            WHEN i.type = 2 And i.is_unique = 1

                THEN ‘Alter Table ‘ +@dbName

                    + ‘.dbo.’ + OBJECT_NAME(ddius.[OBJECT_ID])

                    + ‘ Drop Constraint ‘ + i.name + ‘;’


          END AS ‘SQL_DropStatement’

FROM sys.indexes AS i

INNER Join sys.dm_db_index_usage_stats ddius


        And i.index_id =ddius.index_id

INNER Join partitionCTE AS cte


        And i.index_id =cte.index_id

WHERE ddius.database_id =@dbid


    (ddius.user_seeks +ddius.user_scans +ddius.user_lookups) ASC

    , user_updates DESC;


Provides a more granular view.  Not only do you see if an index is being used…you see how it is being used.  Another cool feature is that the Drop or Alter Index statements are generated for you.





Both of the queries reference the sys.dm_db_index_usage_stats DMV, return similar results, and work great! 


Why use both?  You may or may not…but it’s not a bad idea to get a second opinion.  In addition, understanding the variations of how the DMV, and its results, can be used is what unlocks some of its mysteries. 


Jason’s query cuts to the chase.  Are the indexes, on tables of substantial size, being utilized?  You can adjust or add additional threshholds to suit your environment.  This would be a great query to put into a Reporting Services report to keep an eye on some of your heavy hitters.


Michelle’s query gives you that extra detail for a more in-depth look.  The fact that the DDL statements are generated is great for documenting any changes you make…this day and age that is critical.  I suppose you could take it a step further and generate the Create and Alter statements that would reverse those changes too.  Management likes peace of mind…


Now…if you haven’t subscribed to, and read, Jason Massie’s, and Michelle Ufford’s, blogs…go do it…the path to enlightenment is much shorter that way.