# Unused Indexes: Detection and Management > [!Note] > Regularly identifying **unused indexes** by analyzing **DMV usage statistics** helps eliminate structures that incur **write overhead** without delivering **query benefits**, reclaiming storage and improving DML performance. In SQL Server, every nonclustered index generates metadata in **sys.dm_db_index_usage_stats** that records how often it is **sought**, **scanned**, **looked up**, or **updated**. By comparing these usage counts against update frequency, you can pinpoint indexes that impose maintenance cost without meaningful query support. Focus on nonclustered indexes with high **user_updates** but low **user_seeks**, **user_scans**, and **user_lookups** to identify candidates for removal. > [!tip] > Before dropping, ensure that the index truly isn’t used by any critical workload—review recent execution plans or run representative tests. |**Aspect**|**Details**| |---|---| |**Identification**|Query **sys.dm_db_index_usage_stats** to capture operations per index (seeks, scans, lookups, updates).| |**Index Types**|Distinguish **Heap** (index_id = 0), **Clustered** (1), and **Nonclustered** (≥ 2) indexes when analyzing usage.| |**Evaluation Criteria**|Target **nonclustered indexes** with **few seeks/scans/lookups** but **many updates**, indicating maintenance cost outweighs benefit.| |**Key Metrics**|- **user_seeks**, **user_scans**, **user_lookups** (measure query benefit)| |- **user_updates** (measure maintenance cost)|| |**Recommended Action**|Drop underutilized indexes that add overhead without accelerating queries; monitor system after removal to confirm no regressions.| --- ## ## References - Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media. - Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.