Find unused index in SQL Server 2005/2008/Denali

If you are aware with Index well or if you have read all my previous articles related to Index, you might aware that Index can increase the speed of SELECT statement but can reduce INSERT/UPDATE/DELETE performance so it is better to remove Unused index, it will not only give benefit to INSERT/ UPDATE/ DELETE but it will free up some disk space resources too.
So, after this clarification you understand the requirement of deleting unused Index, right? But how to find which index has never been used? Well I have written one small snippet of TSQL for the same which I am going to share with you.
Note: statistics you are going to see with below given script, would be refreshed and start collecting data again from zero if you restart you server instance or database.  So, first decide your business cycle, let SQL Server collect data and then run the following script to know exact situation otherwise it may happen that some query runs regularly so you can see its stats and few run only once or twice in a month or a quarter and you don’t see its stats and based on that you drop the index which affect the query when it start running at its regular time after a month or quarter. 
Here is the code which I was talking about, earlier:
–following query will show you which index is never used
select
      ind.Index_id,
      obj.Name as TableName,
      ind.Name as IndexName,
      ind.Type_Desc,
      indUsage.user_seeks,
      indUsage.user_scans,
      indUsage.user_lookups,
      indUsage.user_updates,
      indUsage.last_user_seek,
      indUsage.last_user_scan,
      ‘drop index [‘ + ind.name + ‘] ON [‘ + obj.name + ‘]’ as DropCommand
from
      Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID
      LEFT JOIN  sys.dm_db_index_usage_statsindUsage
            ON
                  ind.object_id =indUsage.object_id
                  AND ind.Index_id=indUsage.Index_id
where
      ind.type_desc<>‘HEAP’ and obj.type<>‘S’
      AND objectproperty(obj.object_id,‘isusertable’) = 1
      AND (isnull(indUsage.user_seeks,0)=0 AND isnull(indUsage.user_scans,0)=0 and isnull(indUsage.user_lookups,0)=0)
order by obj.name,ind.Name
–following query will show you list of ALL index in database
–along with data how many times it get seek, scan, lookup or update 
select
      ind.Index_id,
      obj.Name as TableName,
      ind.Name as IndexName,
      ind.Type_Desc,
      indUsage.user_seeks,
      indUsage.user_scans,
      indUsage.user_lookups,
      indUsage.user_updates,
      indUsage.last_user_seek,
      indUsage.last_user_scan
from
      Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID
      LEFT JOIN  sys.dm_db_index_usage_statsindUsage
            ON
                  ind.object_id =indUsage.object_id
                  AND ind.Index_id=indUsage.Index_id
where
      ind.type_desc<>‘HEAP’ and obj.type<>‘S’
      AND objectproperty(obj.object_id,‘isusertable’) = 1
order by obj.name,ind.Name
Be sure before droping any index, give it a second thought before deleting it. This is usually a good practice if you are doing this on production server.
if you want to refer all other articles related to index, click here.
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

3 thoughts on “Find unused index in SQL Server 2005/2008/Denali

  1. Daniel

    Syntax error in the second query. Drop the comma after the last column of the Select statement “indUsage.last_user_scan,”

  2. Anonymous

    This is very handy command to find the indexes which are not in use.

    One should aware that this may also include indexes which may be used in future as this give result from last update statistics only.

Comments are closed.