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.

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.