Backup Speed

Overview

Every backup writes its own stopwatch into msdb and nothing reads it. backupset keeps the start, the finish, the bytes read and, since SQL Server 2008, the bytes written, which is everything needed to say what a backup costs and whether it is getting worse.

Nobody notices a backup slowing down until the night it runs past the window and into the working day, and by then the change that caused it is weeks in the past.

The Backup Speed page: what each backup costs and whether it is getting worse
The whole page in the How fast view over thirty days. 20,000 backups, 334GB read at an average of 130MB/s, with every rate measured against that database’s own history.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Backup Speed.

Two rates, and the page says which it is showing

Rate What it is What it tells you
Read rate backup_size over the elapsed time. What the database is delivering.
Write rate compressed_backup_size over the same time. What the destination is taking.

When compression is on the two differ by the compression ratio. When the write rate is the one that has collapsed, the problem is the destination rather than the database, and that is a different afternoon: a network share, a backup appliance, a deduplicating target that has filled up.

Every comparison is against the database’s own history

A four terabyte warehouse and a two hundred megabyte lookup database have nothing to say to each other about megabytes a second. A chart that ranks them together says only which one is bigger.

So each database is measured against itself: its usual rate, its last rate, and its worst.

The three views

View What it ranks
How fast Databases by their usual rate. The straightforward view.
What has slowed Databases by how far the recent rate has fallen below the usual one. This is the view that finds the problem before the window does.
Compression The ratio each database achieves, which is what separates the two rates.

The toolbar

Control Options Default
View How fast, What has slowed, Compression How fast
Window 7 days, 30 days, a year 30 days
Backup ledger Opens Backup Ledger.

Reading the chart

The bars: databases and backup kinds ranked by median rate, slowest first
Median read rate, slowest first, over the window in the toolbar. The kind and the typical size sit under each name, because a rate with no size behind it says nothing.

One bar per database and backup kind, with the median rate on the right.

Slowest first rather than fastest, because the bar worth looking at is the short one. The three kinds are drawn separately, since a log backup and a full of the same database are not comparable.

The bars draw the top thirteen of the thirty five. The grid holds them all.

Reading the grid

The grid: usual, last and worst rate for each database and kind
Usual, last and worst side by side, so the comparison is against this database’s own history rather than against another database. Rows reading too quick to time are backups msdb recorded as taking whole seconds only, which the page says rather than dividing by.
Column What it is
Database The database.
Kind Full, differential or log. The three are measured separately because they are not comparable.
Runs Backups of that kind in the window.
Usual rate The median rate across the window. Median rather than mean, so one bad night does not move it.
Last rate The most recent run’s rate.
Worst rate The slowest run in the window.
Typical size The median size, so the rate has something to stand against.
Compression The ratio, or blank where compression is off.
Longest The longest elapsed time in the window. This is the number that hits the window.
Verdict Whether this database has slowed, by how much, and whether it is the read or the write side.

One thing about elapsed time

Elapsed time here includes waiting for the device, because that is all msdb records. A backup that spent three of its four hours queued behind another one reads as four hours, which is what the operator experienced.

The Backup Window report is the page that separates the two.

Where the data comes from

Source What it gives
dbo.backupset backup_start_date, backup_finish_date, backup_size, compressed_backup_size, type.
dbo.backupmediafamily The destination, so a change of target lines up with a change of rate.

compressed_backup_size arrived in SQL Server 2008. On an older instance the write rate column is blank rather than wrong.

Messages you may see

No backup finished in the last 30 days. Backup Ledger says what history is here in total. If there is history and none of it is inside this window, widen the window from the toolbar.

Report Why you would go there
Backup Ledger What history exists, and for which databases.
Backup Status Whether the backups are current.
Backup Time Frames When each backup runs, and what overlaps what.
Backup Size One database’s backups over time, by size rather than by rate.
Growth from Backups The size series the same table holds.

Frequently asked questions

The read rate is fine and the write rate has halved. The database is delivering as fast as it ever did and the destination is taking it more slowly. Look at the target: a filling share, a deduplication appliance under load, a network path change.

Why is the usual rate a median? Because the mean is dragged by the one night the SAN was rebuilding. The median is what a normal night looks like, which is what “usual” has to mean for the comparison to be worth anything.

Full, differential and log are ranked separately. Why? Because a log backup of forty megabytes and a full of four terabytes reach completely different rates on identical hardware, and averaging them produces a number that describes neither.

The rate got worse and nothing changed. The database got bigger. Check Growth from Backups: a rate that is steady in megabytes a second still produces a longer backup every month if there is more to read.