A table without a clustered index still has a physical storage story. Heap tables can accumulate forwarded records when rows grow. A scan then has extra locations to visit even though the logical rows look unchanged.
Find the Heap Tables Before Tuning Them
Heap tables store rows without a clustered index organizing them by key. Nonclustered indexes can still exist on that table. Their row locators point to heap record locations.
That structure suits some short-lived loads. It also behaves differently during updates and deletes. A table's absence of a clustered index is a design choice, not proof that every query must perform badly.
I inventory the heaps before recommending rebuild work. Start with sys.indexes and index_id zero. Join schemas so identical table names remain distinct. Exclude internal objects from the application review.
The output identifies candidates, not automatic repair targets. Ask which tables are staging areas and which stay active for years. Their write patterns make a much better case than the table count alone.
SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
WHERE i.index_id = 0 AND t.is_ms_shipped = 0
ORDER BY s.name, t.name;Create a Row That Can Grow
The sample table has no clustered key. Its variable-length column leaves room for an update to make a row substantially larger. Run the setup in a disposable database.
Generated rows are demonstration input, not an observed storage result. The engine decides actual page placement. Inspect your output instead of assuming the example guarantees a particular forwarding count.
When the row no longer fits its original page, SQL Server can move it and leave a forwarding record behind. Existing heap locators can follow that pointer. That avoids updating every nonclustered locator immediately.
It also adds work to later access. A forwarding record is a storage mechanism. It doesn't duplicate a business row, so ordinary COUNT queries won't reveal its overhead.
CREATE TABLE dbo.HeapGrowthDemo
(
ItemId int NOT NULL,
Notes varchar(4000) NOT NULL
);
INSERT dbo.HeapGrowthDemo(ItemId,Notes)
SELECT TOP (2000) ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id),REPLICATE('x',20)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
UPDATE dbo.HeapGrowthDemo SET Notes = REPLICATE('x',3000)
WHERE ItemId % 2 = 0;Count Forwarded Records in Heap Tables
The sys.dm_db_index_physical_stats function exposes forwarded_record_count for heap storage. Use DETAILED when you need that count, understanding that it reads the object's pages. Restrict the function to the table under investigation.
A full-database detailed scan consumes resources and can interact with other work. Diagnostic queries deserve a scope decision too, particularly on a busy server or availability secondary.
I read page_count, record_count, and forwarding together. One number doesn't establish the user-visible impact. Capture representative query reads and actual plans before maintenance.
A heap used only for one load and an immediate truncate has a different tolerance from a frequently searched table. The useful question is whether this storage condition contributes to a measured workload problem.
In my run, the report showed 1,000 forwarded records, one for each row the update widened. The record_count column read 3,000 for 2,000 logical rows, because a forwarded row and its stub are counted separately.
SELECT index_id, alloc_unit_type_desc, page_count,
record_count, forwarded_record_count
FROM sys.dm_db_index_physical_stats
(DB_ID(),OBJECT_ID(N'dbo.HeapGrowthDemo'),0,NULL,'DETAILED');
Understand What Deletes Leave Allocated
Deleting rows doesn't guarantee that every emptied heap page is deallocated. Locking and operation details affect page release. A table can therefore retain allocated pages after a substantial delete.
Compare used and reserved space rather than assuming logical row removal immediately returns space to the database. Even deallocated database pages don't automatically shrink the physical file on the volume.
TRUNCATE is a different operation with different restrictions and semantics. It deallocates storage but cannot replace DELETE when foreign keys or required row-level behavior prevent it. Don't change an application operation merely to reclaim pages.
A storage benefit needs a correct business operation underneath it. Empty shelves aren't proof that the building needs to be demolished after every shipment.
Keep Staging Heap Tables Simple
A staging table loaded in bulk and then truncated can be a sound heap design. It doesn't carry a long history of expanding updates. The load and scan pattern can favor avoiding clustered index maintenance.
Check the complete pipeline before changing that structure. Adding a key because every table deserves one is a rule without a workload attached.
Measure the stages separately. A clustered index can improve later joins while making ingestion more expensive. A heap can make ingestion straightforward while shifting work to validation and transformation.
Choose based on the total process, including retries and concurrency. Keep a unique constraint where the business needs uniqueness. Heap storage doesn't excuse allowing duplicate business identifiers into a load that requires clean keys.
Choose the Repair That Fits the Table
ALTER TABLE REBUILD rewrites the heap and removes forwarding from that rebuilt storage. A clustered index changes the table's organization instead. Either operation needs working space, logging, and a maintenance plan.
Nonclustered locators are affected by changing the base storage. Don't estimate the whole operation from the size of one heap page report or from the short command text.
The rebuild below keeps the demonstration as a heap. Inspect it afterward with the same physical query. Alternatively, choose a suitable clustered key and test that design in a separate copy.
Don't perform both changes at once and call the difference a forwarding fix. Keep the experiment focused enough to explain which physical choice improved the representative requests.
ALTER TABLE dbo.HeapGrowthDemo REBUILD;
-- Alternative design, evaluated separately from the heap rebuild:
-- CREATE CLUSTERED INDEX CX_HeapGrowthDemo ON dbo.HeapGrowthDemo(ItemId);After the rebuild, the same physical query showed 0 forwarded records and a record_count of 2,000. The inventory query from the first section now listed dbo.HeapGrowthDemo too.
Check Whether the Condition Returns
Which updates made the rows outgrow their original pages? If the same pattern continues, a rebuilt heap can accumulate forwarding again. Maintenance treats the current condition.
Schema design and access patterns decide how quickly it returns. Review variable-width growth, free space choices, and the suitability of a clustered key. A recurring problem in heap tables deserves a design conversation, not only another scheduled rebuild.
Save before-and-after reads and the physical reports from your own server. Recheck the workload after a representative update cycle. Keep staging heaps that serve the pipeline well.
Fix long-lived heaps when evidence supports the change. The inventory is the beginning of the review. It becomes useful when every candidate has a workload, a reason, and an operational owner attached.
For large objects, coordinate detailed inspection with maintenance and replica workloads. Catalog queries identify heaps cheaply, while physical inspection reads storage. Run the expensive step only where it answers a specific question. Keep the table name and inspection mode with each report so a later reviewer understands its scope.
Related reading on this blog: SQL SERVER Heaps: Understanding Their Benefits and Limitations and What is Forwarded Records and How to Fix Them?.
A heap is not an unfinished table, it is a storage design that needs the right workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
