Connections Over Time
Overview
The Connections report shows who is connected right now, with a small 24 hour trend. When an application leaks connections, a pool is resized, or a batch job opens hundreds of sessions at 2am, the question is a different one: did the connections spike, when, and how far above normal?
The Connections Over Time report answers it over a window you pick, from the last hour to the last 12 months:
- a chart of the peak connections in each bucket (the highest reading in it), or the average with the lowest to highest reading drawn as a band behind it,
- the spikes: buckets whose peak is well above the typical peak,
- tiles with the single highest reading, the number of spikes, the typical peak and the latest reading,
- a by hour of day or by weekday profile, to tell a daily batch window from a one-off,
- a grid with one row per bucket.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Connections Over Time |
| Instance reports navigator | Over time group |
| Related Links bar | From Connections |
| Report arrows | Previous is Connections, next is CPU by Database |
Where the counts come from
When Historic Monitoring is set up, the procedure dbHealthConnectionsForOneInstance counts the user sessions on each monitored instance once a minute (sys.dm_exec_sessions rows with session_id above 50) and stores the count in DBHealthHistory.dbo.Connections, with the instance’s iMonitorID and the minute it was taken.
During the 11 PM hour the same procedure compacts the table so it stays small:
- older readings are kept as one row per hour, the peak of that hour, and readings older than 100 days as one row per day, the peak of that day, so a spike survives the compaction,
- counts older than 40 days are rounded (to 10, then to 20 after 80 days, 40 after 150 days and 100 after 365 days),
- rows older than HistoricRetentionDays (365 when it is not set) are deleted.
So minute by minute detail is only there for recent readings; older buckets are built from fewer, hourly or daily, peaks.
The toolbar
| Control | What it does |
|---|---|
| Peak / Average | Which line the chart draws. Peak is the highest reading in each bucket (the default); Average is the mean of the readings, with the lowest to highest drawn as a band. Switching does not query again |
| Over time / By hour (or By weekday) | The time series, or a profile of the window by hour of day. With Day buckets the profile is by weekday. The profile is not offered for Week or Month buckets |
| 1 h / 12 h / 24 h / 7 d / 30 d / 12 mo | The window, ending now. The default is 7 d |
| Bucket size | The buckets the window offers: 15 min or 1 min for 1 h; Hour, 30 min or 15 min for 12 h and 24 h; Day or Hour for 7 d; Week, Day or Hour for 30 d; Month or Week for 12 mo |
| Refresh | Reads the history again |
Picking a window sets its default bucket: 15 min for 1 h, Hour for 12 h and 24 h, Day for 7 d and 30 d, Month for 12 mo. The window, the line and the shape are remembered for the next visit.
Reading the page
The summary line says whether the connections spiked, for example “3 hours with a spike in the last 7 days. Highest 412 on Sep 21 at 2:04 PM, 2.9x typical.”, or “No spikes in the last 7 days. Peak 96 on Sep 23 at 9:15 AM, typical 80.”
The line under it gives the window and bucket size, how many readings over how many buckets, how many buckets had nothing collected, and when the history starts.
The tiles across the top:
| Tile | Meaning |
|---|---|
| Peak | The single highest reading in the window, to the minute, and when it was. Amber when it is a spike, otherwise neutral |
| Spikes | How many buckets are spikes (1.5x typical or more). Amber when there are any, green when none |
| Typical | The median of the bucket peaks, which is what “normal” means on this page |
| Latest | The most recent reading on record, whatever the window |
The chart draws the line you picked. The Peak connections line is marked amber with “N hours with a spike” (in the bucket’s own words) when there are spikes, and “no spikes” when there are none. A bucket the collector recorded nothing for, between the first and last buckets that do have readings, is drawn hatched rather than joined across. History that starts part way into the window is not drawn as a gap. Click a point to select its row in the grid; right-click the chart to copy it.
The footer says what the line is: the highest reading in each bucket, the band of lowest to highest readings, or the profile averaged by hour of day or weekday. The counts are user sessions, sampled once a minute.
What counts as a spike
A bucket is a spike when its peak is both:
- at least 1.5 times the typical peak (the median of all bucket peaks in the window), and
- at least 10 connections above it.
The second test stops an instance that idles at two or three connections from calling every small rise a spike.
The grid
One row per bucket that has readings, newest first. Sort by Peak to find the worst moments.
| Column | Meaning |
|---|---|
| Time | The start of the bucket |
| Peak | The highest reading in the bucket |
| Average | The mean of the readings in the bucket |
| Low | The lowest reading in the bucket |
| Readings | How many readings the bucket holds |
| vs Typical | The bucket’s peak as a multiple of the typical peak |
| Note | spike for a spike; one reading when a bucket coarser than a minute holds only one reading, which may have missed whatever happened in the rest of the bucket |
Hover a row for all of its figures. The right-click menu adds Copy Chart, and the grid exports to CSV and Excel like every other grid.
Requirements and messages
- Historic Monitoring must be set up. If the instance has no DBHealthHistory database, the page says Historic connection not configured.
- The counts are read through the configured historic connection, from
DBHealthHistory.dbo.Connectionsanddbo.instanceMonitor, filtered to this instance’siMonitorID, so one history database can hold several instances. - If the history database has no
dbo.Connectionstable, the page says so and points to Historic Settings to upgrade DBHealthHistory. - If the instance is not in the historic monitoring list, the page says so; add it in Historic Settings.
- If nothing was recorded in the window, the page says so; counts are only saved while the Database Health Monitor collection job is running. Try a longer window.
- The read is given 60 seconds. If it does not finish, the page says so; try a shorter window or a coarser bucket.
- There is no SQL Server version requirement for the report itself. The toolbar stays on the page with every message, so another window can be tried.