Index Scan Drift

Overview

An index can hold a perfectly steady read count while what is happening underneath it changes completely. A seek is the index doing the job it was built for; a scan is it being read end to end. Index Usage Trend counts both as a read and would show that index as flat the whole time it was quietly giving way to scans – a stale statistic, a predicate that stopped matching the leading key, or a schema change upstream.

This report is the one that separates seeks from scans and watches the ratio: a seek share per day, plotted as a sparkline card per index, with a verdict when the second half of the window moves meaningfully against the first.

Nothing new is being tracked. This is the first report in the suite to read seeks and scans as a ratio instead of two more numbers to add to a total, out of the same IndexUsageOverTime history Most Used Indexes and Index Usage Trend already collect.

Historic only

Like Index Usage Trend, there is no real-time equivalent – a seek share only has a shape across collected history. If the historic collection database is not configured, the page says so rather than drawing an empty chart.

Where to find it

A database-level report, under Indexing. Select a database, then open Indexing → Scan Drift. The page title reads Index Scan Drift for <database name>.

The Index Scan Drift report
One card per index. The seek share line runs 0% to 100% on every card, not scaled to its own peak.
Seek share cards
A shared scale is the point here – it is what lets a steady 40% index and a collapsing 90%-to-20% index be told apart by shape rather than by height.

Reading the cards

Unlike Index Usage Trend, every card here is drawn on a fixed 0% to 100% scale, with a dotted line at 50% as a shared reference. A share is already a common unit, so scaling each card to its own peak would flatten exactly the comparison this page exists to make.

Verdict What it means
Drifting to Scans Seek share fell by 15 percentage points or more, second half against first.
Drifting to Seeks Seek share rose by 15 points or more – usually a fix that already landed.
Steady Neither half moved enough to call a trend. The ordinary case.
Not enough history Too few reads, or too few days with a read, to say.

A day with zero reads is left out of the line and out of every average, rather than counted as 0% seeks. Zero reads is not the same fact as zero seeks; averaging a quiet day in at 0% would manufacture a drift that was really just silence.

Reading the grid

The index scan drift grid
Seek Share is the overall figure across the whole window; Change is the move between halves.
Column What it is
Verdict Drifting to Scans, Drifting to Seeks, Steady, or Not enough history.
Change Percentage-point move in seek share, second half against first.
Table · Index Name · Type The index.
Seek Share Seeks as a share of reads, across the whole window.
Reads · Seeks The totals the share is built from.
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

Indexes are ranked by total reads in the window, not by how much they have drifted – the busiest indexes are the ones a silent drift costs the most, so they are the ones worth seeing first.

How to read the report

  1. Start with Drifting to Scans. Cross-check the table’s last statistics update on Statistics – a stale auto-updated statistic is the most common cause.
  2. A widened key is the other common cause. If statistics are current, check whether a query’s predicate changed shape – a range that used to be selective and no longer is.
  3. Drifting to Seeks is usually good news, worth confirming rather than chasing – a statistics update, an index rebuild, or a query fix that already landed.
  4. Double click a card to open Index Fragmentation on that index – fragmentation is a second, unrelated way a seek can degrade into something slower.

Common patterns

Drifting to Scans right after a maintenance window. Check whether statistics were meant to update in that window and did not.

A high-write index Drifting to Scans. Busy write activity can outpace an auto-update threshold between statistics refreshes – see Statistics for how far behind this index’s table currently is.

Steady at a low seek share. Not a drift, but worth a look on its own – an index seeking rarely may never have had the right key order for how it is actually queried.

Report Why you would go there
Index Usage Trend The read count this report divides into seeks and scans.
Most Used Indexes Whether a drifting index is also a heavily used one.
Statistics The most common cause of a drift toward scans.
Index Fragmentation The other way a seek degrades – worth ruling out alongside this.

Frequently asked questions

Why 15 percentage points as the threshold? It is a deliberate floor against ordinary day-to-day noise. A smaller move is common and rarely worth a page telling you about it.

Why is a quiet day excluded instead of shown as 0%? Zero reads and zero seeks are different facts. Excluding the day is what keeps the line honest about what actually happened.

Can an index be both Declining on Index Usage Trend and Steady here? Yes, and it is a useful distinction. Declining reads with a steady seek share is a volume change; a Steady read count with a drifting seek share is a shape change. They are different problems with different fixes.

Why does this report share IndexUsageOverTime with Index Usage Trend rather than collecting its own data? The seeks and scans columns were already being collected for three other reports. This is the first one to read them as a ratio.