Duplicate Indexes
Overview
The Duplicate Indexes report finds sets of indexes on the same table with identical key columns, included columns and filter, ranked by the space cleanup gives back – with the keep/drop plan printed on every row.
The answer is on the page, not in a dialog. Every row says which copy to keep and which to drop, and one toolbar button scripts every safe group at once.
The keeper rule, explicit
When several indexes are identical, one of them has to survive. The rule, in order:
- Clustered wins – it is the table.
- Then a primary key.
- Then a unique index or constraint.
- Then the most read copy.
Everything else in the group is scripted as a drop.
What this fixes
The old report was a pie chart of “waste” over a grid of groups, and it was wrong in ways that produced false duplicates:
- The answer was hidden. The grid showed groups with no index names at all, so which copy to keep and which to drop was invisible, and the cleanup script only existed inside the advisor dialog, one double-click per group. The pie was the wrong form twice over: ranked magnitudes read as bars, not angles, and a slice labelled by a column list identifies nothing.
- Sort direction was ignored. The signature compared column names only, so an index on
OrderDate ASCand one onOrderDate DESCmatched as duplicates, and they are not. - Included columns were compared in unstable order. Includes were sorted by
key_ordinal, but included columns all havekey_ordinal = 0, so identical indexes could compare unequal. Includes are a set, and they are compared name-sorted now. - Filtered indexes were silently excluded on 2008 and newer – but two identical filtered indexes are duplicates too.
filter_definitionis part of the signature now.
Where to find it
A database-level report. Select a database in the tree, then open Duplicate Indexes. The page title reads Duplicate Indexes for <database name>.
Reading the report
The chart is ranked waste bars, one per group, with the plan on each row. Each group gets a Safe or Review verdict.
| Column | What it is |
|---|---|
| Group | Which duplicate set this row belongs to. |
| Action | Keep or Drop, from the keeper rule above. |
| Table | The table. |
| Index Name | The index. |
| Type | Clustered, nonclustered, unique, and so on. |
| Reads | Reads against this copy – what decides the keeper when nothing else does. |
| Size | What this copy occupies. |
| Key Columns | The keys, in order, with sort direction. |
| Includes | The include list. |
| Notes | Why this row is the keeper or the drop. |
The restart caveat, scoped correctly
This is the subtlety worth reading. sys.dm_db_index_usage_stats resets on restart, but that does not invalidate the duplicates – two indexes with the same signature are duplicates whatever the read counts say.
What young counters can do is crown the wrong keeper. When the tie-break falls through to “most read copy” and the counters are hours old, the choice between two otherwise identical copies is close to arbitrary.
So: the duplicate finding is always trustworthy. The which one to keep may not be, if the instance restarted recently.
The toolbar
| Button | What it does |
|---|---|
| Script N Safe Drops | Scripts every group marked Safe in one go. The count is in the button label. |
Refresh |
Reload now. |
How to read the report
- Look at the total the headline gives back. That is what cleanup is worth.
- Work through Safe groups first. Review groups need a human decision.
- Check the keeper on each group. If the instance restarted recently, confirm the choice rather than trusting the read counts.
- Use Script N Safe Drops, read the script, and run it in a window.
- Then look at Unused Indexes. Duplicates and unused indexes overlap, and cleaning both together avoids two maintenance windows.
Common patterns
Two indexes differing only in name. Somebody scripted an index that already existed under another name. The clearest case.
A group where the keeper is a primary key. Correct by the rule – the constraint-backed copy survives.
Indexes that look identical but are not listed. Check sort direction and the filter. ASC and DESC are genuinely different indexes, and the old report wrongly merged them.
A Review group. Something about the group needs judgement – usually a unique index against a non-unique one, where dropping the wrong copy changes what the table guarantees.
Many duplicates on one table. Often the result of repeated missing-index suggestions being applied without checking what existed. Look at Missing Indexes afterwards, and check its Notes column.
Messages you may see
Nothing found:
No two indexes in this database share the same keys, included columns and filter. Nothing to clean up – there are no duplicates to remove.
Timed out:
The duplicate index lookup did not finish in time. A database with a great many indexes can take a while to read; try again.
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 stays until the page is ready.
Related reports
| Report | Why you would go there |
|---|---|
| Unused Indexes | Overlaps with this. Clean both in one window. |
| Missing Indexes | Where duplicates usually come from. |
| Problem Indexes | Disabled indexes and fill factor problems. |
Frequently asked questions
Are OrderDate ASC and OrderDate DESC duplicates? No, and the old report said they were. Sort direction is part of the signature.
Why is a filtered index listed? Because two identical filtered indexes are duplicates too. The old report excluded filtered indexes entirely on 2008 and newer.
How is the keeper chosen? Clustered, then primary key, then unique, then most read. The rule is applied in that order and the Notes column says which step decided it.
Does a restart invalidate this report? Not the duplicates – those are structural. It can affect which copy is crowned the keeper when the tie-break falls through to read counts.
Why did my two identical indexes not appear before? Probably the include-column ordering bug. Includes are a set and are now compared name-sorted; the old query sorted them by a column that is zero for all of them.