Database | Indexes | Unused
This reports on indexes that have not been used since the last service restart. These are candidates for deletion. Unused indexes have the overhead of being updated when a data modification is made without providing any benefit for queries.
WARNING: Just because an index is listed here does not mean it is not needed.
The lifecycle for an application or system process may last longer than the typical uptime of your SQL Servers. Some processes are performed monthly, quarterly or annually and an index may have been created for one of these processes. So don't automatically delete all indexes that show in this report. You need some understanding of the system itself before you can make this decision.
| What It Shows | Comments |
|---|---|
| Table | |
| Index | |
| User updates | The more often an index is updated, the more likely it is affecting performance |
| Index rows | |
| Index columns | The columns that make up the index |
| Include columns | The INCLUDE columns that are stored at leaf level only |
| Drop statement | The TSQL that should be used to drop the index |
This picture shows one of the unused indexes expanded to show the index details and the drop statement.
< Database | Indexes | Missing
Database | Indexes | Redundant >