Tuesday, August 14, 2012

Indexes not used since the last reboot

This query gives you a list of indexes that have not been used since the last reboot.


1)     I’m excluding primary key indexes. Amazing how often they’re listed (i.e. primary access is not via primary key).
2)      I am running this for a specific database (see the “database_id” column in the where clause). You can identify a database’s database id by running the command:
a.       Select db_id(‘insert database name here’)
b.      Or, if you want to go the other way,
                                                               i.      Select db_name(‘insert database id here’)
3)      You get three columns, table name, index name, and the drop command to get rid of the index. It’s sorted by table name / index name
a.       It might be erring on the side of constructive paranoia to generate a create script for each of the indexes you’re about to drop before dropping them, in case circumstances require a quick recreate.

index drops of unused indexes

      'drop index [' + sch.name + '].[' + 'obj.name' + '].[' + i.name + ']'
      sys.dm_db_index_usage_stats ius join
      sys.indexes i on
            ius.index_id = i.index_id and
            ius.object_id = i.object_id join
            sys.objects obj on
            ius.object_id = obj.object_id join
            sys.schemas sch on
            obj.schema_id = sch.schema_id
      database_id = 8 and
      user_seeks + user_scans + user_lookups = 0 and
      i.name not like 'PK_%'
order by

No comments:

Post a Comment