Index Usage Trend
Overview
Most Used Indexes and Unused Indexes both answer how is this index doing right now. Neither can answer is it still doing that. An index that carried a fifth of the database’s reads for a year, and an index that carried a fifth of the database’s reads until three weeks ago and has been silent since, land in the same Critical bucket on Most Used Indexes – both reports only ever look at a running total.
The second index is the one worth knowing about, and by the time its total finally reads low enough to show up on Unused Indexes instead, the history that would have explained why it stopped is outside the collector’s retention window. This report exists to catch that earlier: a small sparkline card per index, showing reads and writes over the window you pick, with a verdict on every card.
This is the first report in the suite to read IndexUsageOverTime as a series instead of summing it into one more total – the same collected history Most Used Indexes, Unused Indexes and Inefficient Indexes already read.
Historic only
There is no real-time equivalent. sys.dm_db_index_usage_stats is a running total with no shape to plot, reset on every service restart. This report needs the historic collection database; if it is not configured, the page says so and points at Historic Settings rather than showing an empty chart.
Where to find it
A database-level report, under Indexing. Select a database, then open Indexing → Usage Trend. The page title reads Index Usage Trend for <database name>.
Reading the cards
Each card shows an index’s daily reads (a filled blue line) and writes (a thin amber line) across the window, with a verdict chip in the corner:
| Verdict | What it means |
|---|---|
| Growing | Meaningfully busier in the second half of the window than the first. |
| Declining | Meaningfully quieter in the second half, without reaching zero. |
| Stopped | Read earlier in the window, silent since – before Unused Indexes would flag it. |
| New | Quiet in the first half, reading since – worth knowing why before it looks unused later. |
| Steady | About the same across the whole window. The ordinary case. |
| Not enough history | The window is short, or too sparse, to call a trend honestly. |
Growing and Declining are decided by comparing the average daily reads in the first and second half of the window, not by the first or last day a row happened to arrive. A day the collector recorded no reads for is a gap in the line rather than a point joined straight through it – joining invents a reading, and it is very good at inventing one exactly where an outage or a dropped collection job needs to be seen.
Reading the grid
| Column | What it is |
|---|---|
| Verdict | Growing, Declining, Stopped, New, Steady, or Not enough history. |
| Change | Percent change, second half of the window against the first. |
| Table · Index Name · Type | The index. |
| Reads | Seeks + scans + lookups over the whole window. |
| Writes | Index maintenance over the whole window – not usage. |
| Days Tracked | Distinct days with at least one recorded read. |
| Window | The span the collector actually returned, inside your chosen time frame. |
| Last Read | How long ago the index was last read. |
The toolbar
| Group | Buttons |
|---|---|
| Time frame | 30D · 90D · 180D · All |
| Top N | Top 8 · Top 16 · Top 30 |
Refresh |
The window shown is what the collector actually returned inside your chosen time frame, not the time frame itself – requesting 90 days from a collector that started six days ago does not draw sixty-four days of invented history in front of it.
History past roughly two months is rounded onto fewer days by the collector’s own retention rollup, so a real week of reading can draw as a single spike with quiet days either side of it. That is the collector choosing storage over resolution, not a gap in what happened.
How to read the report
- Look for Stopped first. These are the indexes about to show up on Unused Indexes, with the explanation still attached.
- Check Declining against Most Used Indexes. A Critical index that is quietly Declining here is worth watching before it changes category.
- New is not always good news. A newly busy index can mean a query changed, not that the index was just created – double click a card to open Index Fragmentation on it.
- Ignore Not enough history until the window has had a few weeks to collect.
Common patterns
A Critical index on Most Used Indexes that is Declining here. Worth a second look before it drops out of Critical on its own.
Stopped, with a recent Last Read. The window rounded an old spike onto a coarser bucket – check the Window column against the collector’s retention rollup before assuming the index truly went silent.
Growing right after a deployment. Usually a query change finding this index for the first time. Confirm with the application team before treating it as a surprise.
Related reports
| Report | Why you would go there |
|---|---|
| Most Used Indexes | The running total this report turns into a line. |
| Index Scan Drift | The same history, read as a seek share instead of a read count. |
| Unused Indexes | Where a Stopped index eventually shows up, once its total catches down. |
| Maintenance Window Finder | When to schedule work on an index this page flags. |
Frequently asked questions
Why is there no real-time mode? A running total has no shape. The trend only exists across collected history.
Why does a quiet day break the line instead of joining straight through? Joining across a gap invents a reading. A break is the honest answer, and it is usually also the more useful one – it points straight at an outage or a stopped collector.
Why is Growing not necessarily good and Declining not necessarily bad? An index Growing because a bad query plan started scanning it is not good news. This report says what changed, not why – Index Scan Drift and Index Fragmentation are usually the next stop.
What counts as “not enough history”? Under eight days of observed window, or under four distinct days with a recorded read. Below that, a half-versus-half comparison is mostly noise.