How to find unused indexes

By this time, it is crystal clear that an index can boost up performance, but it comes at a price. Indexes need space in your desk to accommodate their own B-Tree and get updated each time a DML statement gets executed, so it is a good idea to check for any unused indexes in every business cycle.

Getting ready

Before executing the query to find the unused index, remember that we are going to use a sys.dm_db_index_usage_stats dynamic management view that removes all the data at every restart of a SQL Server instance and starts collecting data from scratch again.

If we just restart the server or SQL Server instance and look for the statistics, it will show that no index is used and will suggest dropping (DROP) all indexes; this is not right. So, keep your SQL Server running for at least one business cycle, and then check for the statistics.

Tip

Business cycles differ from case tocase. Some tables and queries could be in use every day, but some queries for some reports...