Table Growth History

Overview

File Size Over Time and Growth from Backups show a whole database growing, and Table Size shows the tables as they are right now. Neither answers which tables are growing, and how fast? That is the question behind deciding what to archive, partition or purge. Table Growth History answers it from a daily sample of every table’s rows and size kept in DBHealthHistory.

Where to find it

Expand a database in the server tree, then Historic > Table Growth History. The page is offered on SQL Server 2008 and later, for every database except tempdb and model.

Where the numbers come from

The Database Health monitoring job runs trackTableGrowth from trackEvery15Minutes. Once a day it records, for every user table in every online database it can read:

  • rows, and reserved, data, index and unused space, the same split as sp_spaceused and the Table Size report, from sys.dm_db_partition_stats;
  • only tables at or over a minimum size (10 MB by default), to keep the history small.

A database that is offline, restoring or recovering, that the job cannot open, or whose read fails (a secondary that is not readable, for example) is skipped for that day. Rows are kept for 400 days.

The collection can be tuned with rows in DBHealthHistory.dbo.Settings:

Setting Default Meaning
TableGrowthMinimumMB 10 Tables smaller than this are not collected (0 collects every table)
TableGrowthIntervalDays 1 Days between collections (1 to 31)
TableGrowthRetentionDays 400 Days of history kept (0 keeps everything, never less than 100)

Collect Now on the toolbar takes a sample of every database immediately, replacing any sample already taken today.

Reading the page

  • Size Over Time draws the reserved size of the selected tables (up to 10) on each sample day over the chosen period, each with a dashed trend line in the same color. With nothing selected it draws the five fastest growing tables. Select rows in the grid (Ctrl or Shift click) to plot them.
  • Growth Treemap draws one tile per table that grew over the period: the area is its growth and the color its percent growth (50% or more, 20 to 50%, 5 to 20%, under 5%). Click a tile to find the table in the grid.
  • The grid lists every table, fastest growing over the period first: rows and size now, MB and rows per day over 7, 30 and 90 days, the percent growth over the period, the size projected 6 and 12 months out, the data, index and unused split, the number of samples and the last sample date. A table missing from the newest sample (dropped, renamed or now under the minimum size) is gray and says so in Notes.

The 7 days / 30 days / 90 days buttons pick the period the chart, the treemap, the percent column and the ranking use. The per day columns always show all three.

Until there are samples from at least two days the page says Collecting, check back tomorrow, with the number of samples so far.

How the numbers are worked out

  • The growth per day is the least squares slope of the samples in the window, not the first sample against the last, so one purge, rebuild or load does not decide the rate. A window needs samples from two different days.
  • The percent growth is the fitted growth across the window’s samples over the fitted size at the first of them.
  • The projections add the longest window’s rate (90 days, else 30, else 7) to today’s size, for 182.5 and 365 days, and never go below zero.
  • Tables are matched by schema and name, so a table that was dropped and created again keeps one history.

Right click a row for the Go to menu, or use Related Links: Table Sizes, Table Space Breakdown and Partitioned Tables open on the selected table; File Size Over Time and Files show the database files.

Exporting

Right click the grid to export it to CSV or Excel, or to copy the top growers as text. Right click a chart to copy it as an image.

Permissions and versions

The page reads DBHealthHistory.dbo.TableGrowthHistory on the instance itself. With no DBHealthHistory database, no access to it, or a DBHealthHistory older than the collection, the page says so in one line instead of failing. Collect Now needs permission to run DBHealthHistory.dbo.trackTableGrowth, and VIEW DATABASE STATE in each database for its sizes.