Most Used Indexes

Overview

The question this report is opened to answer is which indexes must not be touched.

The old page showed a “Total Usage” figure and the four raw counters from sys.dm_db_index_usage_stats side by side, leaving the reader to work that out with mental arithmetic. Reads against writes is the whole decision and it was never drawn, never divided out, and never sorted on. It is the ranking now, with a verdict on every row.

Reads and writes are counted separately, on purpose

Reads are seeks plus scans plus lookups. Writes are always their own number, because an index maintained on every write is not being “used” by that write – it is being paid for.

That distinction was broken before: the two modes measured different things under the same column heading. Historic Total Usage was seeks + scans + lookups + updates; real time was seeks + scans + lookups. Changing the Mode dropdown silently changed the definition.

The arbitrary cut

The old real-time query hid rows whose total usage was below the database average. Index usage is a hard power law, so the average is dragged up by the top two or three indexes and the cut lands somewhere meaningless. There is a Top N on the toolbar now, and the page says how much of the database it is showing.

Where to find it

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

The Most Used Indexes report
The whole report. A database-level page – select a database in the tree first.
Index usage bars
Ranked by reads, with writes kept as their own number.

Reading the grid

The most used indexes grid
Reads/Write is the column that decides. Seeks against Scans shows the shape of the usage.
Column What it is
Verdict Whether this index is earning its keep.
Reads Seeks + scans + lookups. Drawn with a bar.
% of Reads Share of all index reads in the database.
Reads/Write The ratio that decides. High is an index worth keeping; near zero is one being paid for and rarely used.
Table The table.
Index Name The index.
Type Clustered, nonclustered, and so on.
Size What it occupies.
Seeks · Scans · Lookups The components, kept so you can see the shape of the usage.

Seeks against scans is worth reading. An index serving mostly scans may have the wrong key order for how it is queried.

The toolbar

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

Real Time reads the live DMV; Historic reads collected history. Both now define reads the same way, which they did not before.

How to read the report

  1. Look at Reads/Write. That is the decision, and it is why the report exists.
  2. Check % of Reads. A handful of indexes usually serve most of the reads.
  3. Look at Seeks against Scans. Heavy scanning may mean the wrong key order.
  4. Cross-check against Unused Indexes. This page tells you what to protect; that one tells you what to remove.
  5. Note the Size column before adding anything similar – a heavily used index that is already large is a poor candidate for widening.

Common patterns

One or two indexes serving most of the reads. Normal, and useful to know. These are the ones not to touch.

A large index with high writes and near-zero reads. It will also show on Unused Indexes. This page confirms it from the other direction.

An index with many scans and few seeks. Queries are not using the leading column the way the index expects. Worth looking at key order.

Very different answers in Real Time and Historic. Real Time only covers since the last restart. Historic covers the collection window.

Report Why you would go there
Unused Indexes The opposite end of the same data.
Missing Indexes What is not there yet.
Duplicate Indexes Whether a heavily used index has a redundant twin.

Frequently asked questions

Why are writes not counted as usage? Because an index maintained on every write is not being used by that write – it is being paid for. Mixing them into one “usage” figure hides the decision.

Why did the numbers change between modes before? Because Historic included updates in Total Usage and Real Time did not. Both define reads as seeks + scans + lookups now.

Why is there a Top N instead of showing everything above average? Because index usage is a power law and the average is meaningless. Top N is explicit, and the page says what share of the database you are looking at.

What is a good Reads/Write ratio? There is no universal number. Compare within the database: the indexes at the bottom of this ranking are the ones to look at on Unused Indexes.