Table Space Breakdown
Overview
The Table Space Breakdown report answers the question that comes straight after “which table is biggest”: of the space that table has been given, what is it actually spending it on?
Four things can take up room inside a table, and they are drawn as four bands:
- In-row data – the rows themselves, whether they sit under a clustered index or in a heap.
- Nonclustered index – copies of some of those rows, kept for lookups.
- LOB and row overflow – column content stored away from the row.
- Reserved, never used – space the table has been given and has not written to.
Those four add up to reserved space exactly, so the picture is a partition of the database rather than a selection from it.
This page sits alongside the older Table Sizes report rather than replacing it. Table Sizes ranks tables by how big they are and is the right page when that is the question. This one is the right page when the question is what the size is made of.
Where to find it
A database-level report. Expand the server → expand the database → Real Time → Table Space Breakdown.
No minimum SQL Server version. It reads sys.partitions and sys.allocation_units, which have been there since SQL Server 2005.
Reading the mosaic
Column width is share of table space. The widest column is the largest table. The narrowest columns are pinned to a minimum width so they can still be pointed at, which slightly overstates the very smallest shares; the size and percentage printed under each column are the truth.
Column height is always the full plot, and the bands inside split that height by what the space is made of. Because width is share and height is full, a band’s area is its bytes. A thin sliver in a wide column and a thick band in a narrow one can be compared by eye without doing any arithmetic first.
Band order never changes – data at the bottom, then nonclustered index, then LOB, then unused at the top. A colour means one thing across the whole chart.
The last column is everything past the twelve largest tables, added together and drawn faded. It is there so the widths on screen still add up to the database. The grid below keeps every table regardless.
The four colours
| Colour | Band | What it means |
|---|---|---|
| Blue | In-row data | The rows. Includes heaps, which have no clustered index. |
| Amber | Nonclustered index | Duplicated data you chose to pay for. |
| Violet | LOB and row overflow | Column content stored off-row. |
| Grey, hatched | Reserved, never used | Space allocated and never written to. |
Unused space is hatched rather than solid because it is the absence of anything. The diagonals read as empty, and they survive being printed in black and white, which a flat grey does not.
Colour is never the only signal. The band order is fixed, the legend is always on, wide columns are labelled directly, 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.
The four shapes worth finding
The chart exists to make four different problems look different.
Just big. Mostly blue, with a thin index band. Nothing is wrong with it – it is simply the table. Archiving is the only lever.
Index heavy. More amber than blue. The table carries more nonclustered index than data. That is not automatically wrong, but it is worth checking against Unused Indexes and Duplicate Indexes, both of which are on the right-click menu.
LOB heavy. Mostly violet. The size is column content, not row count. Neither the row count nor index tuning will explain it, and this is the class the older Table Sizes report cannot show at all.
Slack heavy. Mostly hatched. The table has reserved far more than it is using. This is the only one of the four that can be handed back, so it is the one the report calls out: the column is outlined in red, the grid row is chipped, and the advice line under the chart names it.
A table is only flagged as slack heavy when both tests pass: at least 25% of it is unused, and there is at least 512 MB of it. Every table carries a little slack, and a page that flags all of them has flagged none of them.
Composition view
Mosaic and Composition on the toolbar draw the same four bands two ways.
Composition drops the width encoding entirely: every table gets a row of equal length, normalised to 100%, with its size moved to the label at the end. That makes shape comparable between a 40 GB table and a 400 MB one, which the mosaic deliberately does not – in the mosaic the 400 MB table is a sliver, because that is the honest picture of the database.
Use the mosaic to find where the space went. Use composition to find which tables are shaped wrong. The choice is remembered between visits.
The grid
| Column | What it is |
|---|---|
| Shape | Data heavy, Index heavy, LOB heavy or Slack heavy. Coloured to match the band it is named after; Data heavy is left plain. |
| Table | Schema-qualified table name. |
| Rows | Row count from the heap or clustered index. |
| Reserved | Total space allocated to the table. |
| Composition | The same four bands as the chart, in one cell. Sorts by the share that is unused. |
| Data | In-row data pages in use. |
| NC Index | Nonclustered index pages in use. |
| LOB | LOB and row overflow pages in use. |
| Unused | Reserved less used. |
| Per Row | Reserved space divided by row count. The number that says whether a table is large because it has many rows or because its rows are heavy. |
Clicking a column selects its grid row; selecting a grid row highlights its column. Double clicking either one opens the Table Size Advisor.
What this reads, and why it is not the Table Sizes query
The report splits space by allocation unit type rather than by index type, and that difference is the whole point.
- Splitting by index type sums
CLUSTEREDinto one bucket andNONCLUSTEREDinto another. A heap is neither, so a heap comes back with a real total and nothing accounting for where the space went. Splitting by allocation unit meansindex_id0 and 1 are both simply “the rows”, and heaps need no special case. - LOB and row overflow have no bucket at all under the index-type split. They get folded into whichever index owns them, which is exactly what hides the usual reason a table is mysteriously large.
- Sizes stay decimal all the way out of SQL Server. The older query divides pages by 1024 in integer arithmetic, so every size is truncated to a whole megabyte and a table under a megabyte reports as zero.
Unused is reserved less used across all four classes, so it is a real subtraction rather than a leftover, and the four bands add back up to reserved rather than to something near it.
How to read the report
- Look at the widths. That is where the space is. If one column is most of the plot, that table is the database.
- Look for hatching. Anything substantially hatched is space you can get back, and it is the only thing on the page that is.
- Look for violet. A violet-heavy table is large because of its column content. Row counts and index tuning will not move it.
- Look for amber taller than blue. More index than data. Worth a trip to Unused Indexes before it is worth an archiving conversation.
- Read the advice line under the chart. It names the worst of each shape, and says nothing at all when there is nothing worth saying.
Questions
Why is my whole database one blue column? Because it is one table, or nearly. That is a real answer, not a failure of the chart. Switch to Composition to see the shape of the smaller tables at a readable size.
Why does the smallest column look bigger than its percentage says? Columns have a minimum width so they can be read and clicked. The label under the column carries the true size and share.
A table has unused space but is not flagged. Why? Flagging needs both 25% and 512 MB. Below either threshold the space is not worth an outage to reclaim, and flagging it would train you to ignore the flags.
Where did my partitions go? Summed into one row per table. Partitioned Tables is the report that draws them separately.
Does this replace Table Sizes? No. Both are in the tree. Table Sizes ranks; this one dissects.
How do I get the unused space back? Usually an index rebuild on the table, which repacks the pages. A data file shrink is a different operation and a blunter one – see the Files and File Utilization reports before reaching for it.