Having the proper indexes in place is important for performance; however, on occasion indexes are created that are never used. Unnecessary indexes incur overhead for any DML.

Fortunately this is a simple thing to detect.

CREATE TABLE #UnusedIndexes
(database_name sysname,
table_name sysname,
index_id int,
object_id int,
index_name sysname NULL,
user_updates bigint,
last_user_update datetime,
user_seeks bigint,
user_scans bigint,
user_lookups bigint,
system_seeks bigint,
system_scans bigint,
system_lookups bigint,
system_updates bigint)

INSERT INTO #UnusedIndexes
(database_name, table_name, index_id, [object_id],
user_updates, last_user_update, user_seeks,
user_scans, user_lookups, system_seeks,
system_scans, system_lookups, system_updates)
SELECT db_name(us.database_id) as database_name,
object_name(us.object_id, us.database_id) as table_name,
us.index_id,
us.object_id,
us.user_updates, us.last_user_update,
us.user_seeks, us.user_scans,
us.user_lookups,
us.system_seeks, us.system_scans,
us.system_lookups, us.system_updates
FROM sys.dm_db_index_usage_stats us
WHERE us.user_seeks + us.user_scans + us.user_lookups = 0
AND us.database_id > 5
DECLARE @database_name sysname
DECLARE @statement NVARCHAR(2000)
DECLARE UnusedIndexCursor CURSOR FOR
SELECT distinct database_name FROM #UnusedIndexes
OPEN UnusedIndexCursor
FETCH NEXT FROM UnusedIndexCursor INTO
@database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = N'UPDATE ui SET index_name = si.name FROM #UnusedIndexes ui INNER JOIN ' + @database_name + N'.sys.indexes si ON ui.object_id = si.object_id and ui.index_id = si.index_id'
EXEC sp_executesql @sql = @statement
FETCH NEXT FROM UnusedIndexCursor INTO
@database_name
END
CLOSE UnusedIndexCursor
DEALLOCATE UnusedIndexCursor
SELECT * FROM #UnusedIndexes us
ORDER BY us.user_updates DESC
DROP TABLE #UnusedIndexes

This query uses sys.dm_db_index_usage_stats.  In particular it looks for indexes where no seeks, scans, or lookups occur against it.  The final output of the query is ordered by the number of user_updates in descending order to identify which indexes are generating the most overhead.  You’ll notice we put the results in a temporary table.  This is done so we can lookup the index names against the appropriate database.

In the output above we can see that user_updates are occurring on the indexes, but no seeks, scans, or lookups are occurring.  A user_update occurs when an indexed column has a value inserted, updated, or deleted.  From this its pretty clear to see that the index is not being used for lookups.  This is a good indicator that the index is just overhead and not really being used for its real purpose (looking update).

Caveats

These stats get reset based on the version of SQL that you’re running.  Unfortunately some versions reset these stats when an index gets rebuilt.

Here’s a summary of when these stats get rebuilt.  I give credit to Kendra Little’s post here.

Version	Resets on
SQL Server 2005 & SQL Server 2008R2	Reset on database offline/restart.
SQL Server 2012	Reset on ALTER INDEX REBUILD of that index until SP2+CU12 or SP3+CU3. Reset on database offline/restart.
SQL Server 2014	Reset on ALTER INDEX REBUILD of that index until SP2. Reset on database offline/restart.
SQL Server 2016	Reset on database offline/restart.

One additional caveat.  Careful not to drop indexes from foreign keyed columns if you ever plan to perform deletes.  I describe why this can be a problem in this blog post.

Conclusion

This is a simple check you can run on your database.  Removing unused indexes can reduce transaction log overhead.  This is something worth exploring if you have a system showing high amounts of WRITELOG or LOGBUFFER wait. If you are using Azure SQL Database and log activity is a contributing factor to your DTU level you should definitely see if you have unused indexes – it could save you $$$!

SQLGrease and deadlocks

How can SQLGrease help you with fixing your deadlocks?  It collects all the historical information you will need to fix your deadlocks, as well as perform a lot of the tedious work of sorting through deadlock graphs.  Watch our demo to see more.

Watch us diagnose a query timeout with SQLGrease:

See how you can get query level wait events without Query Store:

Free you say?  How can that be?