Explanation of Unused Indexes in SQL Server

The short description of SQL Server unused indexes which covers the reasons why they affect a database execution plan and how they could be found with the help of the sys.dm_db_index_usage_stats system view. It also provides us with a warning regarding the need to check the trend of usage patterns of any index that needs to be removed.

Unused Indexes in SQL Server

Unused indexes are indexes which do not frequently or never get used by the SQL Server in retrieving data on a table. Although they may appear harmless, these indexes have the capability to affect the performance of the database adversely in respect of many aspects. They take up precious storage space and more importantly they cause the write operations like INSERT, UPDATE and DELETE statements to go slower. The reason is that, the SQL server has to uphold these indexes, and, even when not used to enhance query performance.

Identifying Unused Indexes

SQL server monitors the index usage statistics so you can monitor the indexes that are not used. A system view named sys.dm_db_index_usage_stats is the main instrument to perform this purpose. This perspective offers details of the frequency of each of the indexes in various forms of activities.

To find nonclustered indexes that never been used one should ask the following query:

SELECT  
 OBJECT_NAME(i.object_id) AS TableName,  
 i.name AS IndexName,  
 i.index_id,  
 s.user_seeks,  
 s.user_scans,  
 s.user_lookups,  
 s.user_updates  
 FROM  
 sys.indexes AS i  
 LEFT JOIN  
 sys.dm_db_index_usage_stats AS s  
 ON i.object_id = s.object_id AND i.index_id = s.index_id  
 WHERE  
 i.type_desc = 'NONCLUSTERED'  
 AND s.user_seeks IS NULL  
 AND s.user_scans IS NULL  
 AND s.user_lookups IS NULL  
 AND i.is_primary_key = 0  
 AND i.is_unique_constraint = 0;  

Explanation of the Query:
OBJECT_NAME(i.object_id) AS TableName: Given to retrieve table-name that the index is involved with.
i.name AS IndexName: This would fetch the name of the index.
i.index_id: Raises the id of the index.
s.user_seeks,s.user_scans, s.user_lookups, s.user_updates: columns in sys.dm_db_index_usage_stats indicate the number of seeks, scans, lookups and updates that have occurred in the index.
FROMsys.indexes AS i LEFT JOIN sys.dm_db_index_usage_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id: Combines data in the system views sys.indexes and sys.dm_db_index_usage_stats to collect information about the indexes and their use statistics. In order to incorporate indexes even when they do not have any statistics of their usage, the LEFT JOIN is employed.
Where i.type_desc = 'NONCLUSTERED': The result is filtered to contain nonclustered indexes only.
ANDs.user_seeks is null, and s.user_scans is null and s.user_lookups is null: Gets only the indexes that have not been used at all to do seeks, scans, or lookups.
and ANDi.is_primary_key = 0 AND i.is_unique_constraint = 0: Emphasizable indexes are those approaching primary key and unique constraint indexes, but these are most likely to be essential to data integrity and performance.

This question refers to nonclustered indexes, which have not been recently utilized since the last SQL Server restart or even since it has run the last time it was restarted.