What Changed
Overview
The historic database has been collecting waits, CPU, blocking, long running queries, index usage and a DDL audit log for years, and nothing has ever compared one window to another.
This is the report a DBA opens at nine on a Monday.
It costs the monitored instance nothing
This page reads only the historic database. It does not connect to the monitored instance at all, which means it works against a server that is currently down.
That is a useful property on the morning after.
Where to find it
Right-click an instance in the tree, What Changed.
The toolbar switches between Ranked changes, Change log and All dimensions, and the window is 8 h, 24 h or 72 h. The choice is remembered between sessions.
Requirements
| Requirement | Why |
|---|---|
| A configured history database | Every figure on this page comes from it. |
| At least three weeks of collection | With fewer than three baseline samples there is no distribution to compare against, and the page says so rather than inventing one. |
Why the comparison is week over week
The baseline is the same hours in the previous four weeks, not yesterday.
That matters more than it sounds. Workload is weekly, not daily. Comparing Monday 09:00 to Sunday 09:00 produces a page of alarming nonsense: every batch job, every user login, every report run is on a weekly cycle. Comparing it to the last four Mondays at 09:00 produces a page of findings.
The three rules that stop the page being noise
A report that cries wolf gets closed permanently after about a fortnight, so all three are enforced rather than offered as options.
1. Same hour of the week, not the same clock hour
As above.
2. Rank by distance outside the normal range, not by percentage
The median and interquartile range of the four baseline samples say what normal variation looks like for that dimension. A metric that swings widely every week has to move much further before it is reported than one that has been steady.
The chart’s entire axis is that distance, measured in interquartile ranges from the median:
| Band | Distance |
|---|---|
| Critical | 4 IQRs or more above the median |
| Serious | 2.5 to 4 IQRs |
| Watch | 1.5 to 2.5 IQRs |
| Normal | Within 1.5 IQRs |
| Improved | 1.5 IQRs or more in the good direction |
| No baseline | Not enough history to say |
The distance is signed so that positive always means worse, whichever direction that is for the dimension. Page life expectancy and index seeks are the two where a fall is the finding, and the chart says so on those rows rather than leaving the reader to reverse the sign mentally.
3. Say “not enough history” and mean it
With fewer than three baseline samples there is no distribution, and the row says so rather than showing a number.
Small counts get a floor as well. A blocking count going from one to three is a two hundred per cent increase and almost certainly nothing, so a dimension below the floor in both the window and the median is not ranked at all.
Where a dimension has been perfectly steady the interquartile range is zero, and dividing by it would put every such row at infinity. A floor of ten per cent of the median is used instead, which says in effect that a metric with no observed variation is allowed ten per cent before anybody is told.
The part nothing else does
The metric delta and the DDL audit log are already in the same database and have never been on the same page.
Lock waits tripled at 02:20.
is a metric.
Lock waits tripled at 02:20, and at 02:14 an index was dropped on dbo.Orders.
is a root cause.
The Correlated event column carries the schema change most likely to explain the row, and the Change log view has the full list. Index and statistics changes are preferred over everything else, because a CREATE PROCEDURE in the same window is noise on a page about lock waiting.
The correlation is deliberately shallow. The page reports that a change happened inside the same window and leaves the causation to the reader. Claiming that a DROP INDEX caused a lock wait rise would be a correlation dressed up as a diagnosis. What it does do is put both halves on one line.
What is compared
| Group | Dimensions |
|---|---|
| Waits | Wait time per category, folded onto the same taxonomy the Waits report uses. Benign categories are excluded. |
| CPU | Processor utilisation. |
| Blocking | Blocking events, and time spent blocked. |
| Counters | Performance counters, including page life expectancy, where a fall is the finding. |
| Long running | Long running query counts and durations. |
| Index usage | Seeks, where a fall is the finding, and scans. |
Wait types are folded onto the wait categories here in the application rather than in the query, because the taxonomy belongs to the product and not to the history database. That keeps this page speaking the same vocabulary as the Waits report.
Reading the grid
| Column | What it is |
|---|---|
| Rank | Position, and the band. |
| What changed | The dimension. |
| Window | Its value in the window you picked. |
| 4 week median | The middle of the four baseline samples. |
| Distance | How far outside normal, in interquartile ranges. |
| Correlated event | The schema change from the same window most likely to be related. |
The Change log view is one row per recorded change: when, what event, which object, which database and who did it.
The limitation that is stated rather than hidden
Only some of the collectors record which instance a row belongs to. Waits, CPU, blocking and the DDL log carry an instance. The performance counter, index usage and long running query tables do not.
On a history database monitoring more than one instance, those dimensions are left out rather than silently mixed together, and the banner says how many instances are involved. Mixing two instances’ counters into one baseline would produce distances that look precise and mean nothing.
On a history database watching a single instance, everything is compared.
Right-click actions in the grid
| Action | What it does |
|---|---|
| Go to Performance History | The same collected history graphed over time. |
| Go to Structure Change Log | The full DDL list the correlated event column is reading. |
| Copy the query behind this report | The whole batch, to run and adapt yourself. |
Where the data comes from
Every table below is in the history database. All of them are read behind an existence check, so a history database from an older version of the product loses the dimensions it does not have rather than failing the page.
| Source | What it gives |
|---|---|
waitsNormalized, waitsLastWaitType |
Wait time by type, folded into categories. |
CPU |
Processor utilisation. |
blockingOverTime |
Blocking counts and durations. |
PerfCounterOverTime |
Performance counters, including page life expectancy. |
IndexUsageOverTime |
Seeks and scans. |
LongRunningQueriesOverTime |
Long running query counts and durations. |
DDLAuditLog |
The schema changes overlaid on the ranked list. |
instanceMonitor |
Which instance a row belongs to, where the collector records it. |
Messages you may see
Only N of the previous 4 weeks have data for these hours. Not enough to know what normal looks like. Every distance on the page is unreliable until the history catches up, and this is the first thing a reader should see when it applies.
This historic database monitors N instances, and the performance counter, index usage and long running query collectors do not record which instance a row belongs to. Those dimensions are left out rather than mixed together.
This instance was not found in the historic database’s instance list. The waits, CPU and blocking figures are not filtered to it.
Nothing moved outside its normal range in this window. The All dimensions button shows everything that was compared, including the ones that stayed put.
The historic database has recorded nothing for this window. Either the collection has not been running, or the window is outside the retention period.
Related reports
| Report | Why you would go there |
|---|---|
| Performance History | The same collected history, one measure over time. |
| Structure Change Log | The full DDL list behind the correlated event column. |
| Waits | Where a wait dimension leads. |
| Blocking by Hour | Where a blocking dimension leads. |
| Plan Regressions | Whether a query plan changed at the same time. |
Frequently asked questions
Why is a metric that doubled showing as Normal? Because it doubles most weeks. The comparison is against that dimension’s own observed variation, not against a fixed percentage, and that is the whole point of ranking by interquartile range.
Why is a metric that barely moved showing as Critical? Because it has never moved before. Four weeks of an almost identical value make the interquartile range tiny, so a small absolute change is a large distance. The ten per cent floor limits how far this can go, and the raw values are in the grid so the reading can be checked.
Why are some dimensions missing entirely? Either the history database does not have that table, or the collector behind it does not record which instance a row belongs to and this history database watches more than one. The banner says which case applies.
Can I use this while the instance is down? Yes, and that is deliberate. Nothing on this page touches the monitored instance.
Why only four weeks? Because it is long enough for a distribution and short enough to still describe the current workload. A baseline stretching back six months would treat last quarter’s traffic as normal.
Does the correlated event mean the change caused the problem? No. It means a change happened inside the same window. The page puts both facts on one line and leaves the conclusion to you, because anything stronger would be a correlation presented as a diagnosis.