Introduction
With any database platform, unused indexes consume space that they may not need to. Identifying unused indexes in SQL Server is a hot topic on many sites, but most of them only discuss part of the "usage" definition. With this script, we think about two definitions for non-usage:
- Indexes that have never been used - the classic case
- Indexes that have been used, but only have had updates against them - these are indexes that have insert, update and/or delete SQL commands executed against the table, so the indexes require to be maintained. However, these indexes have never been used in a lookup, seek or scan operation, or have true "usage". In other words, if they were removed, they should not negatively affect performance.
The query is provided to solve both cases above, and combines the result set for you. Also, many articles only provide a query that works against a single database, but this script works across all databases in the instance by using the sp_MSforeachdb stored procedure.
SYS.DM_DB_INDEX_USAGE_STATS DMV
The main data source for the query is the sys.dm_db_index_usage_stats DMV and it looks for objects that satisfy both of the above cases. It does not include indexes implementing Primary or Unique keys however, since they are used for underlying constraints. A mistake that other sites make with this DMV is that there can be multiple rows for each index, so the columns must be summed to provide meaningful data.
Results
By default, the query returns 4 character columns with the details of the object, as well as 3 data columns that are good candidates for sorting:
- IndexSizeMB - the size of the index. This is the default sort order, e.g. show the largest unused indexes.
- UserUpdates - the number of updates done for each index. These are the unused indexes with the most updates required.
- IndexSpaceUpdatedGB - product of the first 2 items, and represents the GB of data being updated for possibly no reason because of inserts, updates and/or deletes. This would rank a large index lower if fewer updates are being done, while ranking a smaller index higher because more updates are required.
Small indexes are also excluded, and that is controlled by MinIndexSizeMB variable within the script.
