Performance History
Overview
A performance counter on its own means very little. Page life expectancy of 400 is a crisis on one instance and a Tuesday on another. What the number has been doing is the part that carries the information, and that is what this page is.
Thirteen counters, collected by the historic monitoring job, over a window and a bucket size you pick. The page opens on a headline sentence saying what it found, four cards summarizing the instance, and every counter as a small chart on one screen.
Read it the way you would read a strip chart. You are looking for a step, a slope or a coincidence, not for a value.
Where to find it
An instance level entry. Right-click the server → Instance Level Reports → Performance History.
The page title reads Performance History.
Requirements
- SQL Server 2012 or newer. Older instances get a message saying so rather than a page.
- A historic database on the monitored instance, named
DBHealthHistory. The query reads[DBHealthHistory].[dbo].[PerfCounterOverTime]by three part name on the same connection as the instance, which is how every historic report in this product reads its data. That is also why the page can only ever show you the server on screen, never another one. - A history database new enough to have the counter table. An older one gets a message naming it rather than an error.
- The collection job actually running. The page can only show what was collected. A stretch nobody collected is drawn as hatching and counted on the subtitle rather than being joined across, so a gap in the record never looks like a quiet period.
The line across the top
Above the chart, two lines that were not on this page before.
The headline is the worst thing the page found in this window, written as a sentence. Page life expectancy collapsed on Aug 11, which is what a restart looks like. When there is nothing to report it says what the window looked like instead.
The subtitle is the caveat line: how many findings there are, what window and bucket size the numbers are counted over, how many collections fed them, how many buckets had nothing in them, and when history actually starts. That last one matters, because history that does not reach back to the start of your window looks exactly like a collector that stopped.
Underneath are four cards: the busiest the instance got, where page life expectancy stands, server memory against its target, and how many deadlocks there were.
The four views
The toolbar carries Board, Detail, Compare and Profile. Switching between them never goes back to the server; all four are drawn from the one read.
Board
Every counter as a sparkline card, grouped into five families: Throughput, Compilation, Memory, Buffer IO and Contention. Each card carries the latest value, a status dot, and the trace with its peak marked.
A counter that was zero for the whole window is drawn small and gray rather than being given the same room as the one carrying the finding. On the old page Memory Grants Pending was a blank rectangle taking as much vertical space as Page Life Expectancy.
Every trace is scaled to its own peak, so heights are not comparable between cards. That is what Compare is for.
Click a card to open that counter in Detail.
Detail
One counter, full width.
- The line is the bucket value.
- The band behind it is the lowest to the highest reading inside each bucket. This is the honest answer to “the average of a day hides the peak”, and it is why a five minute stall no longer disappears at Day granularity the way it used to.
- A dashed rule at the median, so a spike reads as a spike rather than as the shape of the whole window.
- The peak and, where a low reading is the finding, the trough, labelled with the value and when.
- Hatching where the collector recorded nothing.
Deadlocks are the one counter drawn without a band. They are counted rather than sampled, so a bucket holds the sum of its intervals and the spread inside it says nothing.
Compare
Several counters on one time axis, each scaled to its own peak. This is the thing thirteen separate charts could not do at all, and where most of the real answers on this page are.
The axis is labelled as a share of each counter’s own peak rather than in any unit, because there is no honest shared scale between megabytes, seconds and reads per second. The legend carries what each peak actually was, so the picture can still be turned back into numbers.
Hover anywhere on the plot and you get one rule down the chart, a ring on every line, and all the readings for that moment in their real units. That is the sentence somebody writes down.
Right-click the chart for the ready made comparisons:
| Comparison | Counters | The question it answers |
|---|---|---|
| Memory pressure | Page life expectancy, Page reads, Total server memory | Is the working set no longer fitting? |
| Plan reuse | Batch requests, Compiles, Recompiles | Are plans being reused, or compiled fresh every time? |
| Workload | Batch requests, Transactions, Page lookups | Did the amount of work change, or only its shape? |
| Contention | Deadlocks, Memory grants pending, Batch requests | Is this contention, or just volume? |
Right-click a row in the grid to add or remove a single counter. Four is the limit; adding a fifth drops the oldest, so you can work down the grid swapping counters in without taking one out first.
Compare opens on whatever this window has findings about, or on Memory pressure when it has none.
Profile
Where the selected counter spent its time, which separates always like this at 2am from spiked once. That is the difference between a batch window and an incident.
At hourly buckets or finer it groups by hour of day. At daily buckets it groups by weekday, which is the same question at the scale the data can answer it. At weekly and monthly buckets it can answer neither, so the button is not offered rather than drawn empty.
A slot with no readings is drawn as an empty outline rather than as a zero. On a counter where zero is the good news, those two look identical and only one of them means the server was quiet.
The window and the bucket size
Time frame sets the window. Granularity sets how much time each bucket covers, and its choices change with the window, because the pairs that make sense are not the same at every scale.
| Time frame | Granularity choices | Opens on |
|---|---|---|
| 1 h | 15 min, 1 min | 15 min |
| 12 h | Hour, 30 min, 15 min | Hour |
| 24 h | Hour, 30 min, 15 min | Hour |
| 7 d | Day, Hour | Day |
| 30 d | Week, Day, Hour | Day |
| 12 mo | Month, Week | Month |
The page opens on 30 d at Day, and remembers whatever you last chose.
A bucket’s headline number is an average over its interval, except deadlocks, which are summed because an average deadlock count per collection interval is not a number anybody can act on. The band on Detail is what stops the averaging hiding an incident: narrow the window and take a finer granularity when you are chasing one, widen it when you are looking for a trend.
Changing either re-runs the query once. Switching view does not re-run it at all.
The thirteen counters
| Counter | Family | What it is |
|---|---|---|
| Batch requests | Throughput | How much work arrived. The workload volume line, and the one to read first. |
| Transactions | Throughput | Transactions started. |
| Compiles | Compilation | Plans compiled. |
| Recompiles | Compilation | Plans recompiled. |
| Page life expectancy | Memory | How long a page survives in the buffer pool. Shown as a length of time. |
| Total server memory | Memory | How much memory SQL Server has. |
| Target server memory | Memory | How much it wants. |
| Memory grants pending | Memory | Queries waiting for workspace memory. Zero on a healthy instance. |
| Buffer cache hit ratio | Memory | Share of page requests served from memory. See the caveat below. |
| Page lookups | Buffer IO | Logical reads. Work done inside memory. |
| Page reads | Buffer IO | Physical reads. Work that had to go to disk. |
| Page writes | Buffer IO | Physical writes. |
| Deadlocks | Contention | Deadlocks in the bucket, counted rather than averaged. |
Total and Target memory belong together, which is why Compare exists. Total climbing towards Target is a normal warm-up after a restart. Total sitting at Target for a long time means SQL Server has taken everything it is allowed, which is only a problem alongside a low page life expectancy. Target dropping is external memory pressure on the machine.
Page lookups against page reads is the memory question. Lookups were satisfied in memory; reads had to go to disk. A rise in reads with lookups flat is the working set no longer fitting.
Buffer cache hit ratio is only recorded as a whole number. The collection job divides one counter by another as integers, so what reaches the history table is 0 or 1 and what this page can show is 0% or 100%. Nothing at read time recovers the lost precision. The page detects this and says so as a finding rather than showing you a confident 100. Use page life expectancy instead, which is the more useful reading anyway.
What the page tells you
The findings are worked out on your workstation from the buckets already fetched, so they cost no extra query. They set the headline, the status dot on each card, and the Status column in the grid.
| Finding | Why it matters |
|---|---|
| A restart | Page life expectancy collapses to nothing and climbs again. Every counter here is cumulative since the service started, so readings either side of that point are not comparable. Nothing on the old page said this, so the cliff was left for you to guess at. |
| Page life expectancy under its floor | The floor is scaled to how much memory the instance has committed, not the flat 300 seconds, which came from a four gigabyte machine. |
| Memory at target | SQL Server has taken everything it is allowed. |
| Memory well under target | The pool is still filling. Readings taken during it are not typical. |
| Memory grants pending | Queries compiled and waiting for workspace memory before they can start. |
| Recompiles above a tenth of compiles | Statistics changing under a cached plan, a RECOMPILE hint, or a temporary table written to after the plan was built. |
| Compiles above a tenth of batch requests | Plan cache churn. Usually unparameterized text sent as literals. |
| Deadlocks in the window | With the worst bucket named. |
| Buckets with nothing collected | So a gap in the record is never mistaken for a quiet period. |
| Every counter at zero | The collection job is running against a stopped service. |
The grid
One row per counter, with the latest, average, median, 95th percentile, peak, low, trend and status. Every figure is read back off the same chart you are looking at, so the grid and the picture above it cannot disagree.
Trend is the last third of the window against the first third, not the last bucket against the first. A trend built on single buckets says “up 400%” every time the window happens to open quietly.
Double-click a row to open that counter in Detail. Right-click for its help page, or to add it to the comparison.
How to read the report
- Read the headline. It is the worst thing the page found, already written out.
- Scan the Board for coloured dots. Gray means the counter was flat all window and there is nothing there.
- Start with Batch Requests. It is the workload, and almost everything else should be read as a ratio against it.
- Use Compare rather than your memory. Page reads rising while page life expectancy falls is one story. Page reads rising while batch requests rise is another and a much less interesting one. Put them on one axis and the answer is immediate.
- Look for steps, not values. A counter that changed level on a particular day changed because something happened that day.
- Narrow the window before drawing conclusions about a spike, or read the band on Detail, which is there so you do not have to.
Common patterns
A step change on one date across several counters. Something was deployed. Compare against What Changed and Structure Change Log for the same date.
Page life expectancy sawtooth, dropping and recovering nightly. Index maintenance or a backup pulling everything through the buffer pool. Profile confirms it: a nightly job shows as a fixed hour rather than as scatter.
Recompiles tracking compiles closely. Plans are not being reused. Plan Cache, One Time Use Queries and Top Queries Needing Params are the three follow-ups.
Total server memory well below target and staying there. Something else on the machine is competing. Memory shows the current split.
Hatching across the plot. The collection job was not running. It is a gap in the record, not a quiet period, and the subtitle counts it for you.
Deadlocks rising with no change in batch requests. Contention rather than volume. Deadlock History and Deadlocks by Hour are the detail.
Where the data comes from
[DBHealthHistory].[dbo].[PerfCounterOverTime], written by the historic monitoring job on the monitored instance itself.
One aggregate, bucketed on the server, returning the mean and the extremes of every counter inside each bucket. The predicate stays on DateAdded alone, which is the table’s unique clustered index, so the window is a seek down it and nothing more. Minimum and maximum cost nothing next to the average that was already being computed, and they are what the Detail band is drawn from.
This page writes nothing. It only reads the history.
Related reports
| Report | Why you would go there |
|---|---|
| What Changed | What was altered on the instance around the date a counter stepped. |
| Historic Overview | The same history for one database, with the waits chart underneath. |
| Waits | What the instance is waiting on now, which is usually the explanation for a counter that moved. |
| CPU by Hour by Day | Recorded CPU as a heat map, for the shape of the week. |
| Memory | The current memory split, against the Total and Target lines here. |
| Plan Cache | The follow-up when compiles or recompiles are the counters that moved. |
| Deadlock History | The graphs behind a rising deadlock count. |
Frequently asked questions
The page says no historic database is configured. Performance History reads recorded history and has nothing to show without it. Configure the historic database for this instance first.
The page says the history database has no performance counter table. PerfCounterOverTime arrives with a newer DBHealthHistory. Open Historic Settings to upgrade it.
Why does the page have hatching across part of it? Because nothing was collected for that stretch. The job was stopped, the instance was down, or history has been purged. It is drawn rather than joined across, because a line over a gap is a reading nobody took.
Why does the chart start part way into my window? History does not reach back that far. The subtitle says when it actually starts, so that this is not mistaken for a collector that stopped.
Why does a spike I know about not appear on the line? It does, in the band behind it. The line is the bucket average and the band is the lowest to the highest reading inside that bucket, so narrow the window if you want the spike to be the line itself.
Why are there only some granularity choices? They are matched to the window. One minute buckets across twelve months would be half a million points and nothing readable.
Why can I not select Profile? Because the bucket size is a week or a month, and neither hour of day nor weekday can be recovered from buckets that coarse.
Why is buffer cache hit ratio always 0 or 100? Because the collection job stores it as an integer division of two counters. It is a known limitation of what was recorded, not of this page, and page life expectancy is the reading to use instead.
Where did Move Chart Up, Collapse Chart and Reset Charts go? They existed because thirteen fixed height charts in a scroller needed manual triage. The Board puts everything on one screen and the grid sorts, so they are no longer needed.
Do my view, window and comparison persist? Yes, they are saved with your settings and restored next time.
Why is this an instance report when it is historic? Because the question is about the instance rather than about one database.
Why is SQL Server 2012 the minimum? The query uses functions that are not available on older builds. Older instances get a message instead of a broken page.