Unclustered Tables
Overview
A heap is a user table with no clustered index. This report finds them, and – more usefully – says which of them are actually a problem.
Heaps are not defects by definition. A staging or audit table that is truncated and bulk loaded is legitimately a heap, and a report that flags all of them is a report people learn to ignore. So every row here carries a verdict rather than just an entry.
Why row count is the wrong ranking
The old report drew a bar per table with the bar length being the row count. That cannot answer the question:
A forty million row heap that is bulk loaded overnight and never scanned is fine as it is. A two hundred row heap scanned ten thousand times an hour is not.
Ranking by size cannot tell those apart, so the chart was decoration and the grid order was close to arbitrary.
The diagnostic that was missing
Forwarded records. When a row in a heap grows too large for its page, SQL Server leaves a pointer behind and moves the row elsewhere. Every read that follows the pointer does an extra I/O, and the pointers accumulate.
This report is the only place in the application a heap is ever examined – every other index report (Fragmented Indexes, Inefficient Indexes, Most Used Indexes, Unused Indexes) filters heaps out entirely – and the old version was not reading forwarded_record_count at all.
There was also no way to act on it: no advisor, no script, no context menu.
Where to find it
A database-level report. Select a database in the tree, then open Unclustered Tables. The page title reads Unclustered Tables for <database name>.
The toolbar
| Group | Buttons |
|---|---|
| Top N | Top 25 · Top 100 · All |
| Scan depth | Full detail · Fast |
Create Script ▾ · Refresh |
Full detail reads forwarded record counts, which means a scan of each heap. Fast skips that. On a database with large heaps the difference is substantial – use Fast to get the list, Full detail when you need the verdict.
Create Script writes and never runs, like the other index pages.
How to read the report
- Use Full detail when you actually need to judge, not just enumerate.
- Look at forwarded records first, not row count. That is the number that costs reads.
- Ask what the table is for. A bulk-loaded staging table is a heap on purpose.
- Look at read activity. A small heap read constantly is worse than a large one nobody touches.
- Script the clustered index for the ones that need it, and read it before running.
Common patterns
A small heap with a very high forwarded record count. Rows that grew after insert – typically variable-length columns updated from empty to populated. Every read pays for it.
A large heap with zero forwarded records, never read. Working as intended. Leave it.
A heap with a nonclustered primary key. Common and often unintentional – a primary key does not have to be clustered, and somebody specified NONCLUSTERED without meaning to.
Every table in a database is a heap. Usually a data warehouse staging layer, or an application that creates tables programmatically without a clustered index.
Related reports
| Report | Why you would go there |
|---|---|
| Missing Indexes | Heaps often need a clustered index rather than another nonclustered one. |
| Table Use | Whether anything reads the heap at all. |
| Unused Indexes | The other side of the index conversation. |
Frequently asked questions
Is a heap always a problem? No. Staging and audit tables that are truncated and bulk loaded are legitimately heaps. That is exactly why the report gives a verdict rather than listing every one.
What is a forwarded record? A row that outgrew its page. SQL Server leaves a pointer and moves the row, so every read that finds it does an extra I/O. Only heaps have them.
Why is this the only report that shows heaps? Because every other index report in the application filters type_desc <> 'HEAP'. If a heap has a problem, this is the only page that will tell you.
Why does Full detail take longer? It reads forwarded record counts, which requires scanning each heap. Use Fast for the list.