Unused Indexes

Overview

The Unused Indexes report finds indexes with zero seeks, zero scans and zero lookups over the window, ranked by how much space dropping them would give back.

Every row carries a verdict saying whether it can actually be dropped, because “unused” and “removable” are not the same thing:

Verdict	Meaning
Drop	Safe to remove. This is what you came for.
Constraint	Backs a primary key or unique constraint. Dropping it drops the constraint.
Clustered	It is the table. Dropping it makes a heap.
Disabled	Already not doing anything. See Problem Indexes.

The headline adds up the reclaimable space and the writes spent maintaining the dead weight – because an unused index is not free. Every insert, update and delete on the table maintains it, and nothing ever reads it.

What this fixes

The old report was a size-only bar over a five-column grid, and it hid the very rows it existed to find:

• Nothing said what you would get for acting. Total reclaimable space was never added up, the write cost was never selected, and no row said whether it could be dropped.
• Indexes never touched at all were invisible. The old query inner-joined sys.dm_db_index_usage_stats, which only has a row once an index has been read or written – so a truly dead index on a quiet table silently vanished from the report meant to find it.
• The usage stats join had no database filter. That DMV is instance-wide, so an index in another database sharing an object_id and index_id could join in and credit this one with traffic it never saw.
• The size join matched on table name without the schema, so two tables with the same name in different schemas crossed their sizes.
• The empty state said “There are no indexes being used for this database”, which is the opposite of what zero rows here means.

---

Where to find it

A database-level report. Select a database in the tree, then open Unused Indexes. The page title reads Unused Indexes for <database name>.

---

The restart caveat

sys.dm_db_index_usage_stats is emptied by a service restart. Counters younger than a day invalidate everything under them – an index that looks unused may simply not have been used since the instance came up.

This used to be buried in an advisor dialog. It is now an amber band on the page itself, because it is the difference between a safe drop and a mistake.

---

Reading the report

The chart is ranked size bars, one per index, with the verdict on each row. The grid carries the same order.

Column	What it is
Verdict	Drop, Constraint, Clustered or Disabled.
Size	What dropping it returns. Drawn with a bar so the grid scans like the chart.
Writes	Writes spent maintaining it. This is the ongoing cost of keeping it.
Table	The table.
Index Name	The index.
Type	Clustered, nonclustered, and so on.
Notes	Why the verdict is what it is.
Last Write	When it was last maintained.

Size next to Writes is the pair to read. A large index with heavy writes is costing you twice – storage and every write on the table – for nothing.

---

The toolbar

Button	What it does
Top 25 · Top 100 · Top 500	How many rows.
Refresh	Reload now.

---

How to read the report

1. Check the restart band first. Young counters make the whole page unreliable.
2. Filter your attention to Drop verdicts. Everything else needs a different conversation.
3. Read Size and Writes together. The best candidates are large and written often.
4. Check Last Write. An index written recently but never read is being actively maintained for nothing.
5. Script the drop, read it, run it in a window.

Common patterns

A large index with heavy writes and zero reads. The clearest win the report produces. Every write on that table maintains it and nothing reads it.

Many unused indexes on one table. Often the result of someone acting on missing-index suggestions without checking what already existed. Look at Duplicate Indexes too.

Everything unused right after a restart. The counters are empty, not the indexes. The amber band says so.

An index marked Constraint. It is not really an index decision. Dropping it changes what the table guarantees.

---

Messages you may see

Nothing found:

Every index in this database has been used – nothing to clean up.

Timed out:

The unused index lookup did not finish in time. A database with a great many indexes can take a while to read; try a smaller Top N.

The read is awaited rather than run on the message loop, so a slow database costs a loading panel over a live window rather than an application that has stopped responding. Past four hundred milliseconds the panel appears and counts the indexes off as they arrive.

---

Related reports

Report	Why you would go there
Missing Indexes	The other half of the trade – dropping one may pay for adding another.
Duplicate Indexes	Overlapping indexes, which are a different kind of waste.
Problem Indexes	Disabled indexes and fill factor problems.

---

Frequently asked questions

Why does an index show as unused when I know it is used? Check the restart band. The counters only cover since the service started.

Why are clustered indexes listed at all? Because a clustered index with no reads is worth knowing about – but it is the table, so the verdict says so rather than suggesting a drop.

Why does the report show indexes with no usage row at all? Because those are the deadest ones. The old report’s inner join dropped them entirely.

What is the Writes column for? It is the ongoing cost of keeping the index. An unused index is not free; it is maintained on every write.

---