Inefficient Indexes

Overview

The Inefficient Indexes report finds indexes written more than they are read, ranked by what they cost to keep.

This is the middle ground between Unused Indexes and Most Used Indexes. An index here is doing something – it just isn’t earning what it costs.

What this fixes

“Inefficient” used to mean seeks + scans + lookups under a thousand – an absolute count with no relation to cost. So:

An index read 999 times against 5 writes was flagged. One read 1,001 times against 40 million writes was not.

Reads per write is the number the decision turns on, and it was never computed, drawn, or sorted on. It is the ranking now.

---

Where to find it

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

---

Reading the grid

Column	What it is
Verdict	Whether this index is worth its upkeep.
Writes	Writes maintaining it. Drawn with a bar – this is the cost.
Reads	Seeks + scans + lookups.
Reads/Write	The ratio the report ranks on.
Table	The table.
Index Name	The index.
Type	Clustered, nonclustered, and so on.
Size	What it occupies.
Notes	Context, including whether it backs a constraint.
Last Read	When anything last used it.

Writes is the bar, not reads – deliberately. On this page the cost is the magnitude worth seeing.

---

The toolbar

Group	Buttons
Top N	Top 25 · Top 100 · Top 500
Mode	Real Time · Historic
	Refresh

Real Time reads the live DMV, which resets on restart. Historic reads the collection window.

---

How to read the report

1. Read Reads/Write. Near zero on a heavily written table is the finding.
2. Check Last Read. An index not read for weeks belongs on Unused Indexes instead.
3. Check the Notes column before acting – a constraint-backed index is not an index decision.
4. Compare against Most Used Indexes to confirm nothing depends on it.
5. Consider modifying rather than dropping. An index read occasionally may just have the wrong keys.

Common patterns

A wide index on a hot OLTP table, read rarely. Every insert maintains it. This is the report’s core finding.

High writes and moderate reads. Not necessarily wrong – check whether the reads are the ones that matter.

Everything looks inefficient just after a restart. Real Time counters are young. Switch to Historic.

---

Related reports

Report	Why you would go there
Unused Indexes	Where an index with zero reads belongs.
Most Used Indexes	Confirm what depends on it.
Duplicate Indexes	An inefficient index may be a redundant copy.

---

Frequently asked questions

How is this different from Unused Indexes? Unused means zero reads. Inefficient means some reads, but far fewer than the writes it costs. Different fixes: one is a drop, the other is often a redesign.

Why is the bar writes rather than reads? Because on this page the cost is the point.

What is a bad Reads/Write ratio? There is no universal number – compare within the database. Anything near zero on a busy table is worth investigating.

---