SQL Server provides a huge amount of performance related information in the Dynamic Management
Views (DMVs). The DMV dm_db_index_usage_stats shows usage information about indexes. This can be
very useful in identifying indexes that have been created but never used. Since maintaining table
indexes does give rise to an overhead when data is updated or inserted, this can give performance
gains as well as saving disk space.
A new row gets added to the dm_db_index_usage_stats DMV whenever an index is used for the first time,
and the figures are subsequently updated each time an index is used. However it’s important to note
that the figures do get reset when SQL Server is restarted.
The following script will quickly identify indexes that are used and those that are not used and
is based on the DMV. It lists all indexes in the database and information about whether the index
is used, and the number of updates, scans, seeks and lookups. For simplicity I’ve only included the
most relevant columns from the DMV, but there are further columns which show usage by internal system
queries and the time when the index was last accessed.
SELECT T.name
[Table Name],
I.name
[Index Name],
-- Index is not
used if it is not in the DMV
-- OR if it is
listed but there are no seeks, scans or lookups
CASE WHEN (DMV.index_id IS NULL OR
(DMV.user_seeks
= 0 AND DMV.user_scans = 0 AND DMV.user_lookups = 0 ))
THEN
'Index is NOT used'
ELSE
'Index is used'
END
[Index Usage],
I.Type_Desc
[Index Type],
ISNULL(DMV.user_updates,0) [User Updates],
ISNULL(DMV.user_seeks,0) [User Seeks],
ISNULL(DMV.user_scans,0) [User Scans],
ISNULL(DMV.user_lookups,0) [User Lookups]
FROM sys.objects AS T
JOIN sys.indexes AS I
ON T.object_id = I.object_id
LEFT JOIN sys.dm_db_index_usage_stats AS
DMV
ON I.object_id = DMV.object_id
AND I.index_id = DMV.index_id
AND DMV.database_id=DB_ID()
WHERE T.type = 'U' -- eliminate system and
internal tables
-- Only want clustered and non-clustered
indexes (not heap)
AND I.Type_Desc IN ('CLUSTERED', 'NONCLUSTERED')
ORDER
BY T.name, I.name
There are other index related DMVs such as dm_db_index_operational_stats that give further information
about indexes, as always there is more information in SQL Server books online.
Related Articles
The following articles may also be of interest :
Link back to this article :
https://www.sqlmatters.com/Articles/Checking for unused indexes.aspx
Keywords
Scripts,indexes,unused,DMV