Dropping Unused Indexes

Question

I am referring the Unused Index Script blog.

I got to know that if Seek = 0, Scan = 0, Lookup = 0, User Update = 0 then we should delete index as it is not needed.

The unused script gives me many indexes but should I delete all those indexes? Can you please help me to understand to identify actual unused index from the given script.

I would like to know the importance between UserSeek, UserScans, UserLookups, UserUpdates column before I delete.

Answer 1

The seeks, scans and lookups are read operations which are beneficial to query performance. The updates are inserts into or updates to the index. These updates are considered a negative side affect of indexing.

The first and only rule in the black art of indexing is: take a backup of the indexes before you change them. Just script them out and save them for later. Be sure to date the file so you can correlate any database performance degradation.

A few things to understand:

1. The columns you are referencing (UserSeeks, etc.) are coming from a MSSQL DMV (Dynamic Management View). These metrics are ephemeral, meaning they are reset every time the service is restarted (such as a server reboot). So keep in mind that the seeks, scans, etc will not reflect historical activity.
2. Even is an index has a lot of updates and few beneficial operations - the beneficial operations may be very important. They (for example) may be responsible for making a query that would otherwise run for an hour execute in a second or so.
3. Even if you don't see beneficial activity on an index for months, it may still be used by some rarely accessed part of the software that uses the software - like some kind of monthly or yearly maintenance.

So again, backup the indexes before you remove anything.