Table Sizes
Overview
The Table Sizes report answers three questions about one database:
- Which tables are big?
- How concentrated is the database? Is the space spread across everything, or is one table most of it?
- Why is a given table big? Is it the number of rows, or the size of them?
The third one is the interesting one and it is the reason the report has a third view. A 900 GB table with fifteen billion narrow rows and a 900 GB table with fifteen thousand rows full of PDFs are the same row in any list sorted by size, and almost nothing you would do about one is what you would do about the other.
This page sits alongside Table Space Breakdown rather than overlapping it. Table Sizes ranks. Table Space Breakdown dissects – of the space one table has, how much is rows, index, off-row content and slack. Both are in the tree, and each is on the other’s menu.
Where to find it
A database-level report. Expand the server → expand the database → Real Time → Table Size.
No minimum SQL Server version. It reads sys.tables, sys.partitions and sys.allocation_units, which have been there since SQL Server 2005. It takes no locks worth the name and does not touch the tables themselves.
The four size bands
One colour convention runs through all three views, and it encodes one thing: the table’s share of the database’s reserved space.
| Colour | Band | Threshold | What it means |
|---|---|---|---|
| Red | Dominant | 25% or more | A restore, a migration or a DR test of this database is really one of this table. |
| Amber | Major | 10% or more | Big enough to be worth knowing about before that job is planned. |
| Blue | Notable | 3% or more | A visible share of the database. |
| Grey | Minor | under 3% | The tail. |
Colour is never the only signal. Every mark is labelled or has a tooltip, the legend is always on, and every value is in the grid. Turning on colour blind mode swaps all four to the Paul Tol steps the rest of the product uses.
Ranking view
The default. One row per table, the twelve largest with a bar each and everything below them summed into a final grey row.
Bar length is reserved space, scaled against the largest table on the chart, and the scale is linear. On a database where one table is four fifths of the space, that means one full-width bar and a row of stubs. That is not a drawing fault – it is the single most useful thing this view says, and a log scale would hide it. The size and the share sit at a fixed column to the right of every bar, and those are what carry the small rows.
Under each table name: the row count, the average reserved bytes per row, whether it is a heap or clustered, and the partition count when there is more than one.
To the right of each bar, under the size, one line naming the thing most worth knowing about that table – a quarter of it never written to, more nonclustered index than data, or most of its space being off-row column content. Nothing is printed when none of those apply.
The concentration ribbon
The strip above the rows is the whole database drawn as one bar, one block per table in size order, each block as wide as that table’s share.
Because it is cumulative, the halfway mark across the ribbon is the halfway mark of the database, which is what the two ticks are on. The caption underneath says the number that matters: half the database is in 1 table, 80% in 2 tables, 240 tables in all.
That sentence is usually the reason to open the page. A database whose space is in three tables gets archived, moved and restored very differently from one whose space is spread across two hundred.
If the row limit has trimmed the list, the remainder of the database is drawn as one quiet block at the end of the ribbon, so the bar always reaches the end.
Treemap view
Area rather than length. Each table is a tile whose area is its reserved space, filled by its size band, laid out squarified so tiles come out as near square as the data allows.
This is the view for a database with more tables than a list of bars can hold. Tiles too small to carry a label are folded into a single Other tile rather than drawn as slivers nobody can read or click, and that tile says how many tables it stands for.
Weight view
Rows across the bottom, reserved bytes per row up the side, both on log scales, bubble area is reserved space.
This is the view that says why a table is big.
- Top left – few rows, heavy rows. The size is what is in the columns. Row counts and index tuning will not move it; Table Space Breakdown will tell you whether it is LOB.
- Bottom right – many rows, light rows. The size is the row count. Archiving or partitioning is the lever.
- Both axes are log because row counts and row widths each span five or six orders of magnitude in an ordinary database, and a linear axis would put everything except the worst row against the origin. The axis captions say so.
The diagonals
Reserved space is rows multiplied by bytes per row. On two log axes that makes a line of constant total size a straight diagonal, so the faint dashed lines labelled 10 MB, 1 GB and so on are a size scale you can read straight off the plot:
- A bubble above a diagonal is bigger than that diagonal’s label.
- A bubble sitting on one is that size.
- How far above says roughly how much bigger.
Only the diagonals that pass near the tables actually on the plot are drawn, at most four of them.
Tables with no rows have no bytes per row, so they have no position on the vertical axis. They are counted in the legend rather than dropped silently at the origin.
The toolbar
| Control | What it does |
|---|---|
| Ranking / Treemap / Weight | Switches the view. The choice is remembered between visits. |
| Top 25 / Top 100 / All | How many tables the chart draws. The grid always keeps every row. |
| Refresh | Re-runs the query. |
The row limit changes the picture, not the arithmetic: shares are always worked out against every table in the database, before the limit trims anything, and the chart says in its footer when it is showing a subset.
Right-clicking the chart offers Copy Chart to Clipboard, which copies the whole chart at its full height rather than the part scrolled into view.
The grid
| Column | What it is |
|---|---|
| Size | Dominant, Major, Notable or Minor. Tinted to match the band; Minor is left plain. |
| Table | Schema-qualified table name. |
| Rows | Row count from the heap or clustered index. |
| Total | Reserved space. Sorts on bytes, not on the printed text. |
| Share | The same bar the chart draws, in one cell, scaled against the largest table. |
| % of DB | Share of every table’s reserved space in this database. |
| Used | Pages actually in use across all four allocation classes. |
| Unused | Reserved less used. What a rebuild could repack. |
| Data | In-row data pages – the rows, whether under a clustered index or in a heap. |
| NC Index | Nonclustered index pages in use. |
| LOB | LOB and row overflow pages in use. |
| Per Row | Reserved space divided by row count. The vertical axis of the Weight view. |
| Partitions | How many partitions were summed into this row. |
Clicking a mark on the chart selects its grid row; selecting a grid row highlights its mark. Double clicking either one opens the Table Size Advisor. The right-click menu also jumps to Table Space Breakdown, Partitioned Tables, Unused Indexes and the Files report.
What changed, and why the numbers moved
This report was rebuilt, and both the picture and some of the figures are different from earlier versions. The differences are corrections, not a change of definition.
Sizes are no longer truncated to whole megabytes. The old query divided pages by 1024 in integer arithmetic, so every size lost up to a megabyte per allocation class and any table under a megabyte reported as zero bytes. Everything now stays decimal until it leaves SQL Server, so small tables have real sizes and large ones are a little larger than they used to read.
LOB and row overflow are accounted for. In-row and row overflow allocation units hang off the partition, but LOB units hang off the heap or b-tree, so the two have to be joined on different columns. The old query joined only on partition_id. The two are the same value on nearly every partition, which is why the shorter join usually appears to work.
Heaps have a size again. The old split was on the index’s type description, summing CLUSTERED into one column and NONCLUSTERED into another. A heap is neither, so a heap came back with a real total and two zeroes. The split is now on index_id: 0 and 1 both simply mean “these pages are the rows”, so heaps need no special case. The old Clust. Index column is now Data for that reason – calling it a clustered index would be the lie.
One row per table, not one per partition. A fifty partition table used to appear fifty times, which made it look like fifty medium tables in any ranking and meant the shares could not add to a hundred. Partitions are now summed and counted, and Partitioned Tables is the report that draws them separately.
The chart draws every table. The old treemap silently stopped at fifty and said nothing about it.
How to read the report
- Read the ribbon caption first. How many tables hold half the database is the fact that changes what you do next.
- Look at the top bar. If it is red and full width, that table is the database, and no plan for this database is really a plan until it accounts for it.
- Switch to Weight for anything surprising. A table you did not expect to be big will be in one of two corners, and which corner it is in decides whether the conversation is about archiving or about column content.
- Read the advice line under the chart. It names the dominant table, the heaviest rows and the worst slack, and says nothing at all when there is nothing worth saying.
- Go to Table Space Breakdown when you want to know what one table’s space is made of.
Questions
Why is one bar full width and all the others tiny? Because that is the database. The bars are linear on purpose. The numbers to the right of each bar are there so the small rows are still readable, and the treemap is the better picture when you want to compare the tail against itself.
Is the size the table on disk, or the data in it? Reserved space – every page allocated to the table, used or not. Used and Unused split it, and Unused is the part a rebuild could repack.
Why is Per Row bigger than my rows actually are? It is reserved space divided by row count, so it carries the table’s share of index pages, off-row content and unused space as well as the rows. It is a shape indicator, not a row width.
Why is a table missing from the Weight view? It has no rows, so it has no bytes per row. The legend says how many of those there are.
Why do these numbers not match Large Tables? Large Tables ranks across the whole instance from partition statistics and floors at 1% of a database. This one is one database, from allocation units, with no floor.
Where did the row numbers go? The old first column was the row ordinal. It is only true while the grid is in load order and becomes a lie the moment you sort by anything, so it was dropped.
Does this replace Table Space Breakdown? No. Both are in the tree, and each is on the other’s right-click menu. This one ranks; that one dissects.