Long Running History

Overview

The Long Running History report shows the long-running query samples the monitor service recorded over time.

Two columns make it useful, and neither was visible before:

CPU time against elapsed time

300 seconds elapsed on 295 seconds of CPU is a very different problem from 300 seconds elapsed on 2 seconds of CPU.

The first is a query doing genuine work – a scan, a sort, a bad plan. The second spent almost all its time waiting for something. Same duration, completely different investigation. This is the single most diagnostic pair on the page.

The blocking session id

A sample with a blocking session id is not a slow query at all – it is a victim. It was fast; something else held a lock. Chasing it as a performance problem wastes an afternoon.

The collector had always written both columns. The old report simply never displayed them.

Where to find it

Two versions, differing only in whether the history is filtered by database name:

  • Instance level – every long-running sample on the instance.
  • Database level – one database.

They used to be two near-identical copies; they are now two thin subclasses over one implementation.

The page title reads Long Running History for <subject> (<range>).

The Long Running History report
The whole report.
The long running timeline
Samples on a timeline – clusters point at a batch window, a deployment or a nightly job.

Requirements

This is a historic report. It reads the DBHealthHistory repository, populated by the monitor service. Without collection configured there is nothing to show.

Because it reads history rather than the live instance, it works when the monitored server is unavailable – which is often exactly when you want it.

The long running history grid
CPU against elapsed, and the blocking session id – the two columns the old report never showed.

The toolbar

Group Buttons
Range Last Hour · 24 Hours · 7 Days · 30 Days
View Samples · By Query
Refresh

Samples shows every recorded sample on a timeline – when the slowness happened.

By Query groups them by statement – what was slow, and how often.

Those answer different questions. Samples finds an incident; By Query finds a pattern.

How to read the report

  1. Pick the range that matches the complaint. “It was slow this morning” is Last Hour or 24 Hours, not 30 Days.
  2. Check the blocking session id column first. Anything with a value is a victim, not a cause. Set those aside.
  3. Compare CPU against elapsed on what remains. Close together means the query is working; far apart means it is waiting.
  4. Switch to By Query to see whether one statement dominates.
  5. Switch back to Samples to see whether they cluster in time – a batch window, a deployment, a nightly job.
  6. Take the waiting ones to Waits and the working ones to CPU by Query or Missing Indexes.

Common patterns

Elapsed far greater than CPU, no blocking session. Waiting on something that is not a lock – storage, memory grants, or a parallelism wait. Waits is the next page.

Elapsed close to CPU. Genuine work. A missing index, a bad plan, or a query that simply has a lot to do.

A cluster of samples all with the same blocking session id. One session blocked everything. The blocker is the finding; the samples are collateral.

Samples clustered at the same time each day. A scheduled job overlapping user work.

One statement dominating By Query but spread evenly in Samples. A chronically slow statement rather than an incident.

Report Why you would go there
Waits What the waiting queries were waiting on.
Sessions What is blocking right now.
Deadlocks by Database Where blocking became fatal.
CPU by Query The cost side of the queries doing real work.

Frequently asked questions

Why is a query with a blocking session id not a slow query? Because it was not slow – it was blocked. Its elapsed time measures somebody else’s transaction, not its own work.

What does it mean when CPU time is nearly equal to elapsed time? The query spent its time working rather than waiting. Look at the plan, the indexes, and how much data it touches.

Why are there two versions of this report? Scope. One filters the history by database, the other does not. They share an implementation.

Why does this work when the server is down? Because it reads collected history rather than the live instance.

Why is nothing here? Either nothing ran long enough to be sampled in the range, or historic collection is not configured for this instance.