Definitions More Info.
Definition ID14.118
Definitionkullanilmayan indexler - unused indexes microsoft Hakan
Definition Description/*
Each index not only brings benefits but also cost efforts when data are updated.
But when the index is never used, these are unnecessary costs and the index could be removed.

This Transact-SQL statement list all indexes which haven't been used since the last server start, whereby only user tables and views are considered.
Disabled indexes are excluded as well as heap.
Primary keys and unique indexes are also excluded, because they are part of business logic and required for FK constraints.

Works with SQL Server 2005 and higher versions in all editions.
Requires VIEW SERVER STATE permissions.

-- Unused indexes
SELECT + '.' + AS ObjectName
,OBJ.type_desc AS ObjectType
, AS IndexName
,IDX.type_desc AS IndexType
FROM sys.indexes AS IDX
LEFT JOIN sys.dm_db_index_usage_stats AS IUS
ON IUS.index_id = IDX.index_id
AND IUS.object_id = IDX.object_id
INNER JOIN sys.objects AS OBJ
ON IDX.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0 -- Exclude MS objects
AND OBJ.type IN ('U', 'V') -- Only user defined tables & views
AND IDX.type > 0 -- Ignore heaps
AND IDX.is_disabled = 0 -- Disabled indexes aren't used anyway
AND IDX.is_primary_key = 0 -- Exclude PK => FK constraints / part of business logic
AND IDX.is_unique = 0 -- Exclude unique indexes => part of business logic
AND IUS.object_id IS NULL
ORDER BY ObjectName
Record Date24-03-2024 14:58:15