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_stats indUsage

            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_stats indUsage

            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”