Indexes are database objects that are useful for when it comes to improving your SQL Server performance. The more indexes you have, the more overhead you add for maintaining those indexes.
Part of doing a SQL Server health check, is to identify if there are any unused indexes. Having those indexes around is likely doing more harm than good when it comes to performance.
To find out if an index is not used in our environment, we need to check if there have been any SCANS, SEEKS or LOOKUPS on the index since the SQL Server started. We can use the Dynamic Management View (DMV) named sys.dm_db_index_usage_stats to help us here.
I created the script below to find all unused non-clustered indexes in a SQL Server database.
SELECT DB_NAME(ius.database_id) AS [Database], OBJECT_NAME(ius.object_id) AS [TableName], i.name AS [IndexName]
, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates
FROM sys.dm_db_index_usage_stats AS ius
INNER JOIN sys.indexes AS i ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects AS o ON ius.object_id = o.object_id
WHERE database_id > 4
AND i.type_desc = 'NONCLUSTERED'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND o.type_desc = 'USER_TABLE'
AND ius.database_id = DB_ID() -- comment this line if you want all databases
AND (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0
Feel free to manipulate the script to suit your own needs.
This script also shows how many updates are applied to it by the user_updates column. This is an indication of the maintenance work that is carried out by SQL Server.
Keep in mind that the results displayed are from when SQL Server started. In order to find this out, you can execute the below query.
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info
Let me know if you have any questions in regards to this or any enhancements I can add to the scripts.
Shashane Welgama's Blog 