Heaps are one of those SQL Server internals most DBAs overlook until performance problems start showing up. If you’ve ever seen high page fragmentation, forwarded records, or tables that get scanned millions of times, you’ve likely dealt with the side effects of heaps.
This post walks through what heaps are, how to identify problem ones, and how to troubleshoot and fix them using DMVs, examples, and a step-by-step workshop.
What is a Heap?
A heap is a table without a clustered index. SQL Server stores rows in no particular order, using internal pointers (Row IDs) to find data. Every nonclustered index on a heap points to those physical Row IDs, not a logical key.
That’s fine for small lookup tables or staging areas. But for transactional or reporting tables, heaps can cause inefficiencies over time.
The Common Heap Problems
1. Forwarded Records
When a row grows due to an update (e.g., a VARCHAR column expanding) and no longer fits on the same data page, SQL Server moves it and leaves a pointer behind. That creates extra I/O and slows down reads.
Check forwarded rows:
SELECT forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.YourTable'), 0, NULL, 'DETAILED');If forwarded_record_count > 0, it’s a signal to rebuild or convert to a clustered index.
2. Fragmentation
Since heaps have no logical order, any insert or delete leaves pages scattered. Repeated loads or truncations can cause 90–99% fragmentation even on small heaps.
Check fragmentation:
SELECT avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.YourTable'), 0, NULL, 'DETAILED');- <10%: fine
- >50%: consider rebuild or clustered conversion
3. Excessive Scans
Without a clustered index, queries that can’t use a nonclustered index must perform full table scans. This is often the biggest performance hit.
Identify heavy scans:
SELECT
i.type_desc,
us.user_seeks,
us.user_scans,
us.user_lookups
FROM sys.dm_db_index_usage_stats AS us
JOIN sys.indexes AS i
ON us.object_id = i.object_id AND us.index_id = i.index_id
WHERE us.database_id = DB_ID()
AND us.object_id = OBJECT_ID('dbo.YourTable');If user_scans is much higher than user_seeks, your queries are scanning the heap frequently.
When to Rebuild a Heap
A rebuild fixes fragmentation and forwarded records but doesn’t change table structure.
ALTER TABLE dbo.YourTable REBUILD;This operation:
- Reclaims space and defragments pages
- Removes forwarded records
- Requires a schema mod lock (brief blocking on busy systems)
You can also use ALTER TABLE REBUILD WITH (ONLINE = ON) in Enterprise Edition.
When to Convert to a Clustered Index
If your heap is heavily read, updated, or joined, adding a clustered index is usually a permanent fix.
The clustered index stores data in logical key order and makes nonclustered indexes point to that key instead of physical locations.
Example:
CREATE CLUSTERED INDEX CIX_YourTable_ID
ON dbo.YourTable (YourPrimaryKeyColumn);Choose:
- A narrow, unique, ever-increasing column (like an identity key)
- Avoid frequently updated or random GUID columns
Using DMVs to Prioritize Work
You can easily rank your heaps and clustered tables by page count, fragmentation, and scan frequency.
;WITH phys AS (
SELECT object_id, index_id, page_count, avg_fragmentation_in_percent, forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED')
),
usage AS (
SELECT object_id, index_id, user_seeks, user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
)
SELECT
s.name AS schema_name,
t.name AS table_name,
i.type_desc,
p.page_count,
p.avg_fragmentation_in_percent,
p.forwarded_record_count,
ISNULL(u.user_seeks,0) AS user_seeks,
ISNULL(u.user_scans,0) AS user_scans
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.indexes i ON i.object_id = t.object_id
JOIN phys p ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN usage u ON u.object_id = i.object_id AND u.index_id = i.index_id
WHERE i.index_id IN (0,1)
ORDER BY p.page_count DESC, u.user_scans DESC;From this output:
- High page count + high scans → add clustered index
- Low fragmentation + low scans → leave it
- Forwarded records > 0 → rebuild
Workshop: Diagnosing and Fixing a Heap
Scenario:
You run a query and notice it’s slow on table client_histories.
- Check fragmentation and forwarded records
SELECT forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.client_histories'), 0, NULL, 'DETAILED');2. Check usage patterns
SELECT type_desc, user_seeks, user_scans, user_lookups
FROM sys.dm_db_index_usage_stats
WHERE object_id = OBJECT_ID('dbo.client_histories') AND database_id = DB_ID();3. Interpret results
- Fragmentation = 0%
- Forwarded records = 0
- User scans = 4,500,000
➜ The table is being scanned constantly. It’s a heap without a clustered index.
4. Fix
CREATE CLUSTERED INDEX CIX_client_histories_ID
ON dbo.client_histories (client_id);5. Validate improvement
Wait an hour or two of normal activity
Rerun DMV query
Scans should drop sharply and seeks increase
Best Practices
- Keep heaps only for:
- Small staging tables
- ETL temporary loads
- Bulk insert / truncate cycles
- Add clustered indexes for:
- Tables over ~1,000 pages
- Tables joined frequently
- Tables with heavy updates or reads
- Monitor regularly
- Schedule weekly DMV snapshots
- Export top 50 fragmented or scanned heaps to Excel for tracking
References
- Microsoft Docs: sys.dm_db_index_physical_stats
- Microsoft Docs: Heaps (Tables without Clustered Indexes)
- Paul Randal – “The problem with heaps” (SQLSkills)
- Kimberly Tripp – “The Clustered Index Debate Revisited”
- Brent Ozar – “Forwarded Records and How to Fix Them”
Final Thoughts
Heaps aren’t bad by definition — they’re just misunderstood.
The key is knowing when a heap is acceptable and when it’s silently killing your performance. By using DMV data and simple rebuild or index scripts, you can clean up your storage engine and keep your queries fast and predictable.
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.
