Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 240

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
GO
How it works...
Generally, we get all necessary information from the dynamic management view
sys.dm_db_index_usage_stats , but to know index ID, index name, and index
type, it is mandatory to join the sys.Indexes view, and to know the table name,
it is mandatory to know the Sys.Objects view.
Generally, whenever an index is used, it fills up some value in the sys.dm_db_index_
usage_stats dynamic management view in the user_seek , user_scan , or user_lookup
columns. If the index has never performed seek, scan, and lookup, there is no need to keep
that index (which utilizes disk space and increases overhead in DML statements). Instead, we
can remove it and claim the disk space.
There's more...
Again, apply your expertise while deciding whether the index is actually used, and use the
DROP INDEX command generated by the given query. The use of an index depends on the
business cycle, and you might be running some HR or other report annually, so removing an
index might reduce the performance of those reports that use the index we have removed.
You might not see any scan/seek/lookup operator at the moment, because SQL Server might
have restarted in a recent week or month, while the report is run annually. There are two more
reasons given here that may change your decision about dropping the index:
F If the index is a Primary Key or a unique key, it can be there for the sake of data
integrity even though it is not shown in any scan/seek/lookup
F A unique index assists the optimizer in building a more efficient execution plan, even
though index itself isn't used, by providing information about data distribution