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, drawn as thirteen small charts stacked down one scrolling page. Every chart shares the same time frame and the same granularity, so a spike in one lines up with a spike in another by eye.
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 configured for this instance. Without one the page says so and stops.
- The history 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. - The collection job actually running. The charts can only show what was collected, so a page with a flat section usually means the job was not running then, not that the instance was idle.
The two dropdowns
Time Frame sets the window. Granularity sets how much time each bar 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 |
|---|---|
| Last 1 Hour | 15 mins, 1 min |
| Last 12 Hours | Hour, 30 mins, 15 mins |
| Last 24 Hours | Hour, 30 mins, 15 mins |
| Last 7 Days | Day, Hour |
| Last 30 Days | Week, Day, Hour |
| Last 12 Months | Month, Week |
The page opens on Last 30 Days at Day granularity.
Every bar is an average over its interval, not a peak. That matters more than it sounds. At Day granularity a five minute stall is averaged into twenty four hours and disappears completely. When you are chasing an incident, narrow the time frame and take the finest granularity available; when you are looking for a trend, widen it.
Changing either dropdown re-runs the query and rebuilds every chart.
The thirteen counters
| Chart | What it is |
|---|---|
| Average Batch Requests a Sec | How much work arrived. The workload volume line, and the one to read first. |
| Average Cache Hit Ratio | Percentage of page requests served from memory. |
| Average Memory Grants Pending | Queries waiting for a memory grant. Anything consistently above zero is memory pressure you can feel. |
| Average Deadlocks | Deadlocks per second, averaged. |
| Average Page Life Expectancy | How long a page survives in the buffer pool. |
| Average Page Lookups Sec | Logical reads. Work done inside memory. |
| Average Page Reads Sec | Physical reads. Work that had to go to disk. |
| Average Page Writes Sec | Physical writes. |
| Average SQL Compilations Sec | Plans compiled. |
| Average SQL Recompilations Sec | Plans recompiled. |
| Average Target Server Memory MB | How much memory SQL Server wants. |
| Average Total Server Memory MB | How much it has. |
| Average Transactions Sec | Transactions started. |
Target and Total memory belong together. Total climbing towards Target is a normal warm-up after a restart. Total sitting well below Target for a long time means something outside SQL Server is taking the memory back. Target dropping is external memory pressure on the machine.
Page Lookups against Page Reads is the memory question. Lookups are reads that were satisfied in memory; reads had to go to disk. A rise in reads with lookups flat is the working set no longer fitting.
Rearranging the page
Right-click any chart for four items.
| Item | What it does |
|---|---|
| Move Chart Up | Moves this chart one position higher. |
| Move Chart Down | Moves it one position lower. |
| Collapse Chart | Shrinks it to a title bar, keeping its place. |
| Show Chart | Expands it again. |
The order and the collapsed state are saved and come back next time, per installation rather than per instance. Collapsing the counters you never look at and moving the two or three you do to the top turns this into a page you can read in five seconds.
Reset Charts in the toolbar throws that layout away and reloads the page with the default order, everything expanded.
The More Info link
Each chart carries a DatabaseHealth.com link that opens the page explaining that specific counter: what it measures, what a reasonable value looks like, and what moves it. It is the fastest route from this line went up to here is what that means.
How to read the report
- Start with Batch Requests a Sec. It is the workload. Almost everything else on the page should be read as a ratio against it, in your head if not on screen.
- Look for steps, not values. A counter that changed level on a particular day changed because something happened that day: a release, a patch, a new index, a data load that never got cleaned up.
- Line up two charts. Page Reads rising while Page Life Expectancy falls is one story. Page Reads rising while Batch Requests rises is another and it is a much less interesting one.
- Narrow the window before drawing conclusions about a spike. At Day granularity you are looking at an average of 86,400 seconds.
- Check Memory Grants Pending on any memory question. It is the counter that is zero on a healthy instance and non-zero on one that is short, with no judgement required.
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. Normal, and worth confirming the recovery is complete before the business day starts.
Recompilations tracking compilations 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.
A flat section in every chart at once. The collection job was not running. It is a gap in the record, not a quiet period.
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.
Each counter is cast defensively before it is averaged, so one unreadable sample does not take a whole chart with it. The rows are then grouped to the chosen granularity by truncating the timestamp to that boundary and averaging within it.
The cache hit ratio is stored as a fraction and shown as a percentage, which is why that chart runs 0 to 100 rather than 0 to 1.
This page writes nothing. It only reads the history.
Related reports
| Report | Why you would go there |
|---|---|
| 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 Target and Total lines here. |
| What Changed | What was altered on the instance around the date a counter stepped. |
| Plan Cache | The follow-up when compilations or recompilations are the counters that moved. |
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.
Why does the page have gaps? Because nothing was collected for that period. The collection job was stopped, the instance was down, or history has been purged.
Why does a spike I know about not appear? Because bars are averages over their interval. Narrow the time frame and pick the finest granularity offered.
Why are there only some granularity choices? They are matched to the window. One minute bars across twelve months would be millions of points and nothing readable.
Do my chart order and collapsed charts persist? Yes, they are saved with your settings and restored next time. Reset Charts puts them back to the default.
Why is this an instance report when it is historic? Because the question is about the instance rather than about one database. It reads the same history database everything else historic does.
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.