Understanding Heaps in SQL Server: Troubleshooting, Tuning, and When to Rebuild or Index

---

A heap is a table without a clustered index. SQL Server stores rows in no particular order, tracking them through internal physical Row IDs rather than a logical key structure. For small staging tables and bulk-insert-then-truncate patterns, heaps are appropriate and efficient. For transactional or reporting tables that accumulate updates and reads over time, heaps develop specific problems that show up as forwarded records, fragmentation, and excessive table scans.

This article covers the three core heap problems, how to identify them with DMVs, when to rebuild versus when to add a clustered index, and a complete workshop for diagnosing and fixing a real heap problem.

1 The Three Heap Problems Beginner

Heaps cause performance problems through three distinct mechanisms. Understanding which one is present determines the correct fix.

Problem	Cause	Impact	Fix
Forwarded records	Row grows too large for its current page after an UPDATE	Extra I/O on every read that encounters the pointer	Rebuild the heap
Fragmentation	INSERTs and DELETEs scatter pages over time	Inefficient sequential reads, wasted space	Rebuild the heap or add clustered index
Excessive scans	Queries cannot use nonclustered indexes for all access patterns	Full table scans on every unindexed access path	Add a clustered index on the appropriate key

2 Forwarded Records Beginner

When a row in a heap is updated and the updated row no longer fits on its original data page, SQL Server moves the row to a new page and leaves a forwarding pointer at the original location. Any subsequent read that finds the pointer must follow it to the new location, adding an extra I/O operation per forwarded record encountered. On a heap with millions of forwarded records, this overhead is significant and grows over time as updates continue.

-- Check forwarded record count for a specific table
SELECT
    forwarded_record_count,
    page_count,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.YourTableName'),
    0,          -- index_id 0 = heap
    NULL,
    'DETAILED'
);

-- Any forwarded_record_count above zero is worth investigating
-- Large counts relative to page_count indicate a rebuild is overdue

-- Check forwarded records server-wide to prioritize across all heaps
SELECT
    OBJECT_SCHEMA_NAME(ps.object_id)    AS SchemaName,
    OBJECT_NAME(ps.object_id)           AS TableName,
    ps.forwarded_record_count,
    ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, 0, NULL, 'SAMPLED') ps
WHERE ps.index_id = 0                   -- heaps only
  AND ps.forwarded_record_count > 0
ORDER BY ps.forwarded_record_count DESC;

3 Fragmentation Beginner

Because heaps have no logical ordering, insertions and deletions scatter pages across the file without any attempt to maintain physical order. A heap that has seen many DELETEs may have pages that are mostly empty sitting between pages that are full. This wastes storage and makes range reads slower because SQL Server reads more pages than the data requires.

-- Check fragmentation for a specific heap
SELECT
    avg_fragmentation_in_percent,
    page_count,
    forwarded_record_count,
    record_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.YourTableName'),
    0,
    NULL,
    'DETAILED'
);

-- Fragmentation interpretation:
-- Under 10%:  acceptable, no action needed
-- 10 to 30%:  consider rebuilding if the table is large and frequently read
-- Over 30%:   rebuild or convert to clustered index

4 Excessive Scans Beginner

Every nonclustered index on a heap stores the physical Row ID of each row as the row locator. When a query cannot be satisfied by a nonclustered index alone, SQL Server must scan the entire heap to find matching rows. On large heaps with millions of rows, this is the most damaging performance problem of the three because no amount of rebuilding fixes it without structural change.

-- Check access patterns for a specific table
SELECT
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_scan
FROM sys.dm_db_index_usage_stats        us
JOIN sys.indexes                        i
    ON i.object_id = us.object_id
   AND i.index_id  = us.index_id
WHERE us.database_id = DB_ID()
  AND us.object_id   = OBJECT_ID('dbo.YourTableName');

-- Interpretation:
-- user_scans much higher than user_seeks = queries are scanning the heap
-- This usually means queries filter on a column with no supporting index
-- A clustered index on the most common filter column often resolves this

5 When to Rebuild a Heap Intermediate

Rebuilding a heap removes forwarded records, defragments pages, and reclaims space without changing the table structure. It is the right fix when fragmentation or forwarded records are the problem but the access patterns do not justify adding a clustered index, or when the heap is a staging table that will continue to be used as a heap.

-- Rebuild a heap: removes forwarded records and defragments
ALTER TABLE dbo.YourTableName REBUILD;

-- Enterprise Edition: online rebuild reduces blocking duration
ALTER TABLE dbo.YourTableName REBUILD WITH (ONLINE = ON);

-- Verify improvement after rebuild
SELECT
    forwarded_record_count,
    avg_fragmentation_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.YourTableName'),
    0,
    NULL,
    'DETAILED'
);
-- forwarded_record_count should be 0
-- avg_fragmentation_in_percent should be near 0

6 When to Convert to a Clustered Index Intermediate

A clustered index stores rows in logical key order and replaces the physical Row ID pointers in all nonclustered indexes with the clustered key. This eliminates forwarded records permanently (rows move to their correct logical position during updates), improves range scan efficiency, and makes point lookups through nonclustered indexes far more efficient.

-- Add a clustered index to convert a heap to a clustered table
-- The clustered index key choice matters significantly

-- Good clustered index key characteristics:
-- Narrow (one or two columns)
-- Unique (or made unique with a uniquifier)
-- Ever-increasing (identity INT or BIGINT is ideal)
-- Stable (rarely or never updated)

CREATE CLUSTERED INDEX CIX_YourTable_ID
ON dbo.YourTableName (YourIdentityColumn);

-- Enterprise Edition: online creation avoids long blocking
CREATE CLUSTERED INDEX CIX_YourTable_ID
ON dbo.YourTableName (YourIdentityColumn)
WITH (ONLINE = ON);

-- Verify the table is no longer a heap
SELECT
    i.name,
    i.type_desc,
    i.index_id
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.YourTableName')
ORDER BY i.index_id;
-- index_id 0 = heap, index_id 1 = clustered index
-- After conversion, index_id 0 should no longer appear

7 Prioritizing Heap Work Across the Database Intermediate

This query combines physical stats and usage statistics to rank all heaps in the current database by size, scan frequency, and forwarded record count. Use it to identify which heaps need immediate attention and which can wait.

;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')
    WHERE index_id = 0          -- heaps only
),
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()
      AND index_id = 0
)
SELECT
    s.name                              AS SchemaName,
    t.name                              AS TableName,
    p.page_count,
    ROUND(p.avg_fragmentation_in_percent, 1) AS FragmentationPct,
    p.forwarded_record_count,
    ISNULL(u.user_seeks, 0)             AS UserSeeks,
    ISNULL(u.user_scans, 0)             AS UserScans,
    -- Recommended action based on evidence
    CASE
        WHEN ISNULL(u.user_scans, 0) > 100000 AND p.page_count > 1000
            THEN 'Add clustered index: high scan count on large heap'
        WHEN p.forwarded_record_count > 1000
            THEN 'Rebuild heap: forwarded records present'
        WHEN p.avg_fragmentation_in_percent > 30 AND p.page_count > 100
            THEN 'Rebuild heap: high fragmentation'
        ELSE 'Monitor: no immediate action needed'
    END                                 AS Recommendation
FROM sys.tables                         t
JOIN sys.schemas                        s  ON s.schema_id = t.schema_id
JOIN phys                               p  ON p.object_id = t.object_id
LEFT JOIN usage                         u  ON u.object_id = t.object_id
ORDER BY p.page_count DESC, ISNULL(u.user_scans, 0) DESC;

8 Workshop: Diagnose and Fix a Problem Heap Advanced

This workshop follows the exact investigation steps for a slow heap table. Use it with any slow table on a non-production instance, or follow through with the client_histories scenario below.

Step 1: Check fragmentation and forwarded records

SELECT
    forwarded_record_count,
    avg_fragmentation_in_percent,
    page_count,
    record_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('dbo.client_histories'),
    0,
    NULL,
    'DETAILED'
);

Step 2: Check usage patterns

SELECT
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.last_user_scan
FROM sys.dm_db_index_usage_stats        us
JOIN sys.indexes                        i
    ON i.object_id = us.object_id
   AND i.index_id  = us.index_id
WHERE us.object_id   = OBJECT_ID('dbo.client_histories')
  AND us.database_id = DB_ID();

Step 3: Interpret the results

In this scenario the output shows fragmentation at 0 percent, forwarded records at 0, but user scans at 4,500,000. The table is being scanned constantly despite no physical health problems. The heap structure itself is the issue: queries are filtering on a column that has no supporting index, forcing full scans on every execution.

Step 4: Add a clustered index on the most common filter column

CREATE CLUSTERED INDEX CIX_client_histories_client_id
ON dbo.client_histories (client_id);

-- If client_id is not unique, SQL Server adds a 4-byte uniquifier automatically
-- To make it explicitly unique if needed:
CREATE UNIQUE CLUSTERED INDEX CIX_client_histories_client_id
ON dbo.client_histories (client_id, history_date);

Step 5: Validate after normal workload runs

-- Wait for a representative period of normal activity (one to two hours minimum)
-- then recheck usage stats
SELECT
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups
FROM sys.dm_db_index_usage_stats        us
JOIN sys.indexes                        i
    ON i.object_id = us.object_id
   AND i.index_id  = us.index_id
WHERE us.object_id   = OBJECT_ID('dbo.client_histories')
  AND us.database_id = DB_ID();

-- Expected result: user_seeks increased significantly, user_scans decreased
-- Queries that filtered on client_id now use the clustered index seek path

References

• Microsoft Docs: Heaps (Tables without Clustered Indexes)
• Microsoft Docs: sys.dm_db_index_physical_stats
• Paul Randal (SQLskills): Forwarded Records and Fragmentation
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: SQL Server Fragmentation Guide
• SQLYARD: SQL Server DMV Reference Cheat Sheet