Maintenance Window Finder

Overview

Every maintenance job in most environments runs on a schedule a person typed in once, against no evidence about when this particular database is actually quiet. IndexUsageOverTime already carries a timestamp on every row the collector writes; this is the first report to group that timestamp by hour of day.

The heat map answers the database-wide question – when is the whole database quiet – and the grid beneath it answers the narrower one for whichever index is about to be rebuilt. A reporting index busy all business day and a batch index busy only overnight can carry the same total activity and want opposite maintenance windows; the database-wide picture alone cannot tell them apart.

Historic only

The day by hour picture only exists across collected history. If the historic collection database is not configured, the page says so rather than drawing an empty chart.

What the heat map cannot promise

IndexUsageOverTime only ever stores a nonzero delta – a genuinely silent hour and an hour the collector never reached look identical in this data, both drawn as zero activity. That is a real limit on what this page can tell you, not a defect in it, and the page’s own subtitle says so.

Where to find it

A database-level report, under Indexing. Select a database, then open Indexing → Maintenance Window. The page title reads Maintenance Window Finder for <database name>.

The Maintenance Window Finder report
Every day in the window as its own column, every hour as its own row – a full rectangle, even where an hour never had a row.
The activity heat map
Colour is distance from this sheet’s own typical hour, in both directions – hot is busy, cool is quiet, and both are worth knowing.

Reading the heat map

Each cell is one calendar day at one hour of day, coloured by how far that hour’s combined reads and writes sit from the typical hour on the sheet – not from zero. That is a deliberate choice: on most databases a handful of hours carry most of the traffic, and a scale anchored at zero would leave the whole sheet looking uniformly warm. Hover a cell for the exact count; the number is also printed in the cell itself.

Look for columns of cool cells that repeat across several days – that is a real, recurring quiet stretch, not a one-off quiet night. A single quiet cell surrounded by busy ones is more likely a fluke than a maintenance window worth scheduling around.

Reading the grid

The per-index detail grid
Busiest Hour and Quietest Hour are computed across all twenty-four hours for that index alone, ignoring which day each reading landed on.
Column What it is
Table · Index Name · Type The index.
Activity Total reads and writes across the whole window.
Busiest Hour The hour of day (00 to 23) this index is read or written the most.
Quietest Hour The hour of day this index is touched the least.

The grid is ranked by total activity – the indexes a maintenance window matters most for are the ones actually carrying load, not the ones that happen to sit at the top alphabetically.

The toolbar

Group Buttons
Time frame 7D · 14D · 28D · 42D
Top N Top 8 · Top 16 · Top 30
Refresh

The time frame defaults to seven days and tops out at forty-two – six weeks of day columns is already close to what a sheet this size can show clearly. A day column past that point would ask a lot of one screen for very little extra signal.

How to read the report

  1. Find the coolest recurring block on the heat map first. That is your candidate window, checked against the whole database rather than one table.
  2. Cross-check it against Busiest Hour and Quietest Hour for the specific index you are about to rebuild – a hot reporting index can want a different window than the database as a whole.
  3. Confirm with a longer time frame before trusting a short one. A single unusually quiet week can look like a pattern that a month does not confirm.
  4. Treat a zero cell as “nothing recorded,” not as a guarantee. The collector only stores nonzero deltas, so an hour it never sampled reads the same as an hour that was genuinely silent.

Common patterns

A clear overnight trough, every day of the window. The straightforward case – schedule around it with more confidence than a single quiet night would earn.

No quiet hour at all, on a database that is read around the clock. Worth knowing before scheduling a rebuild on a guess – this is the database telling you there is no free window to find, only a least-bad one.

One index quiet all day, busy all night. A batch or ETL table. Its Quietest Hour can sit in the middle of the database’s own busiest block, which is exactly the case this report exists to catch.

Report Why you would go there
Index Usage Trend Whether the index you are scheduling around is growing or declining.
Index Fragmentation What actually needs the maintenance window you just found.
Most Used Indexes Confirms an index is worth building a schedule around at all.

Frequently asked questions

Why is the heat map coloured by distance from typical instead of a straight 0 to 100% scale? Index activity is a hard power law – a handful of hours usually carry most of the traffic. A zero-anchored scale would make almost the whole sheet look the same color; distance from typical is what actually separates a busy hour from a quiet one.

Why does the grid compute Busiest Hour and Quietest Hour separately from the heat map? The heat map answers the database-wide question. The grid answers the same question for one index at a time, and a single hot index can have a very different pattern than the database as a whole – see the batch-table example above.

Can a zero cell mean the collector was simply not running that hour? Yes, and the page says so. IndexUsageOverTime only stores a row when at least one read or write happened; there is no separate record of an hour the collector watched and saw nothing.

Why cap the time frame at 42 days? Past six weeks of day columns, the sheet is wider than it is useful – a shorter, more recent window is usually the more honest picture of what “now” looks like.