Long Running by Hour by Day

Overview

The Long Running by Hour by Day report shows when queries were slow.

The distinction that makes this page useful: a query being slow and the server being busy are not the same event, and this page read against CPU by Hour by Day tells them apart. Slow queries during a CPU peak are a load problem. Slow queries when the server was quiet are a query, blocking or storage problem – and that is a completely different investigation.

Hours run down, days across, and each cell is one hour. Darker means more long-running queries were recorded in that hour.

Click any cell to see the queries themselves.

The Long Running by Hour by Day report
Where the slow hours are, at a glance. Read this against the CPU heat map – slow when busy and slow when idle are different problems.

Where to find it

An instance-level historic report. Right-click the server in the tree and open it from the historic reports. The page title reads Long Running by Hour by Day for <server name>.

Reading the heat map

Each cell is one hour of one day; darker means more long-running queries were recorded.

The ramp is monotone in perceptual lightness, so magnitude reads from lightness alone – in greyscale, on a projector, or under any colour vision deficiency. Cell text picks black or white per fill and always clears 4.5:1 contrast.

Dark mode inverts the ramp’s anchor so hot advances rather than recedes. Colour-blind mode uses a single blue hue across the same lightness steps.

No data is not zero

An hour where nothing was recorded is drawn differently from an hour that recorded no slow queries. A gap in collection should not read as a fast hour.

Clicking through

Click any cell to open the long-running query history for that hour – what ran, and for how long. A dark cell becomes a list of statements.

The toolbar

Button What it does
Show More Adds a week to the range.
Show Less Removes a week.

One to six weeks in one-week steps, with the buttons hiding at the ends.

The range is shared with the other by-hour-by-day reports. For this page that sharing is the feature – the whole method is comparing this map against the CPU one, and they have to cover the same window for the comparison to mean anything.

Requirements

A historic report reading LongRunningQueriesOverTime from DBHealthHistory, populated by the collection service. Without collection there is nothing to draw, and it works when the monitored server is unavailable.

What counts as “long running” is set by the collection configuration, not by this page. If the map is uniformly dark or uniformly empty, that threshold is the first thing to check.

How to read the report

  1. Put it beside CPU by Hour by Day. That comparison is the point of the page.
  2. Slow and busy – a load or capacity problem.
  3. Slow and quiet – a query plan, blocking, or storage problem. Usually the more interesting of the two.
  4. Look for hours that are always slow. A recurring band is a scheduled job, not bad luck.
  5. Click into the worst hour to see the statements.
  6. Widen the range before concluding a monthly pattern exists.

Common patterns

Slow hours that match the CPU peaks exactly. The server is simply busy. Capacity, or work that could move off-peak.

Slow hours with no CPU behind them. Waiting rather than working – locks, or storage. Check Blocking by Hour by Day and Disk Latency by Hour by Day at the same hours.

A dark band during the maintenance window. Expected – index rebuilds and CHECKDB are long-running by nature. Worth confirming they finish before the business day.

Slow first thing every morning. A cold cache: plans and buffer pool both empty after a nightly restart or maintenance.

Dark cells spreading over successive weeks. Something is degrading. Widen to six weeks and the trend is usually obvious.

Uniformly dark everywhere. The long-running threshold in the collector is probably set too low to be useful.

Report Why you would go there
Long Running History The queries themselves, with CPU against elapsed time.
CPU by Hour by Day The comparison that separates busy from slow.
Blocking by Hour by Day Slow because something else held a lock.
Disk Latency by Hour by Day Slow because storage was slow.
CPU by Query Which statements cost the most.

Frequently asked questions

What counts as long running? Whatever the collection service is configured to record. The threshold is not set on this page.

How is this different from Long Running History? That report lists the queries. This one shows when they happened, so you know which period to open.

Why compare it with the CPU map? Because slow-when-busy and slow-when-idle have different causes and different fixes. The two maps side by side answer that in seconds.

What does a blank cell mean? Nothing was recorded – a collection gap, not a fast hour.

Why does changing the weeks here affect the other heat maps? One shared setting, so the maps you are comparing always cover the same window.