Find unused index in SQL Server 2005/2008/Denali

If you are aware with Index well or if you have read all myprevious articles related to Index, you might aware that Index can increasethe speed of SELECT statement but can reduce INSERT/UPDATE/DELETE performanceso 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 requirementof deleting unused Index, right? But how to find which index has never beenused? Well I have written one small snippet of TSQL for the same which I amgoing to share with you.
Note: statistics you are going to see with below givenscript, would be refreshed and start collecting data again from zero if yourestart you server instance or database.  So, first decide your business cycle, let SQLServer collect data and then run the following script to know exact situationotherwise it may happen that some query runs regularly so you can see its statsand few run only once or twice in a month or a quarter and you don’t see itsstats and based on that you drop the index which affect the query when it startrunning at its regular time after a month or quarter. 
Here is the code which I was talking about, earlier:
–followingquery 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
–followingquery will show you list of ALL index in database
–along withdata 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 thoughtbefore deleting it. This is usually a good practice if you are doing this onproduction 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 adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: