How to check unused index in SQL Server

When it comes to SQL Server performance, indexes are crucial and indexes are a fantastic tool for enhancing the performance of SELECT queries, but they have a negative impact on data updates. Operations like INSERT, UPDATE, and DELETE duplicate data already present in the table by modifying the index. This lengthens the time it takes for transactions to complete and for queries to execute, which frequently leads to locking, blocking, deadlocking, and execution timeouts. Maintaining unused indexes helps in our efforts to reduce storage requirements and database engine overhead.

 

SELECT  

o.name AS TableName, 

i.name AS Indexname, 

i.is_primary_key AS PrimaryKey, 

s.user_seeks + s.user_scans + s.user_lookups AS NumOfReads, 

s.user_updates AS NumOfWrites, 

(SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS TableRows, 

'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'DropStatement' 

FROM sys.dm_db_index_usage_stats s  

INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  

INNER JOIN sys.objects o ON s.object_id = o.object_id 

INNER JOIN sys.schemAS c ON o.schema_id = c.schema_id 

WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 

AND s.databASe_id = DB_ID()  

AND i.type_desc = 'NONCLUSTERED' 

AND i.is_primary_key = 0 

AND i.is_unique_constraint = 0

order by TableName

 

 Result 

 

--Second Query, you can also try this

SELECT

            OBJECT_NAME(i.OBJECT_ID) AS ObjectName

            ,i.name AS UnusedIndexName  

            ,8 * SUM(au.used_pages) AS IndexSizeInKB

            ,CASE

                        WHEN i.type = 0 THEN 'Heap' 

                        WHEN i.type= 1 THEN 'Clustered'

                        WHEN i.type=2 THEN 'Non-Clustered'  

                        WHEN i.type=3 THEN 'XML'  

                        WHEN i.type=4 THEN 'Spatial' 

                        WHEN i.type=5 THEN 'Clustered columnstore index'  

                        WHEN i.type=6 THEN 'Nonclustered columnstore index' 

                        WHEN i.type=7 THEN 'Nonclustered hash index.'

            END index_type

            ,'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(i.OBJECT_ID) AS DropStatement

FROM sys.indexes AS i

LEFT JOIN sys.dm_db_index_usage_stats AS dius

            ON dius.OBJECT_ID = i.OBJECT_ID

                        AND i.index_id = dius.index_id

                        AND dius.database_id = DB_ID()

INNER JOIN sys.partitions AS p

            ON p.OBJECT_ID = i.OBJECT_ID

                        AND p.index_id = i.index_id

INNER JOIN sys.allocation_units AS au

            ON au.container_id = p.partition_id

WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexable') = 1

            AND OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexed') = 1

            AND dius.index_id IS NULL

            OR (dius.user_updates > 0

                        AND dius.user_seeks = 0

                        AND dius.user_scans = 0

                        AND dius.user_lookups = 0)

GROUP BY OBJECT_NAME(i.OBJECT_ID), i.name, i.type

ORDER BY OBJECT_NAME(i.OBJECT_ID)