SQL Server Identify Heap Tables Without Clustered Indexes

calendar May 15, 2026 / · 9 min read · sql server heap tables sys.tables sys.indexes sys.dm_db_index_physical_stats sys.allocation_units sys.partitions performance database administration sql scripts  ·
Share on: linkedin copy

A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and sys.dm_db_index_physical_stats to identify every heap in the current database, report its size and row count, and surface forwarded record counts so DBAs can prioritize which heaps need a clustered index.

Purpose and Overview

In SQL Server, a heap is a table where sys.indexes.type = 0 — meaning there is no clustered index organizing the rows by a key. Data pages are allocated as rows arrive and tracked through Index Allocation Map (IAM) pages rather than a B-tree structure. For read-heavy workloads without covering nonclustered indexes, this means every query that cannot use a nonclustered index scans every allocated page in the table in whatever order the IAM chain presents them — unpredictable, non-sequential I/O.

The more insidious problem is forwarded records. When an UPDATE causes a variable-length row to grow beyond the free space on its current data page, SQL Server cannot simply move the row to a new page — existing nonclustered indexes reference the row's physical location (Row Identifier: file number, page number, slot number). Instead, SQL Server writes the updated row to a new page and leaves a forwarding stub at the original location. Every subsequent read that visits the original page must follow the forwarding pointer to the new page, doubling the I/O for that row. On heaps with many nonclustered indexes, the problem compounds: each nonclustered index entry points to the heap RID, and each RID lookup that hits a forwarding stub costs an extra page read.

This script, adapted from the ktaranov/sqlserver-kit community repository with forwarded-record detection from Brent Ozar Unlimited's diagnostic patterns, reports heap tables with their allocated size, used size, row count, nonclustered index count, and forwarded record count. Run it in the target database context.

Code Breakdown

The query identifies heaps via sys.indexes.type = 0, aggregates size from sys.allocation_units, and fetches forwarded record counts with a per-table OUTER APPLY to sys.dm_db_index_physical_stats.

 1-- SQL Server Heap Tables Without Clustered Indexes
 2-- Source: adapted from ktaranov/sqlserver-kit community repository
 3-- Identifies user tables with no clustered index (heap storage) and reports their size
 4-- Run in the target database context
 5
 6SET NOCOUNT ON;
 7
 8SELECT
 9    SCHEMA_NAME(t.schema_id)                              AS SchemaName,
10    t.name                                                AS TableName,
11    SUM(p.rows)                                           AS RowCount,
12    CAST(
13        SUM(a.total_pages) * 8.0 / 1024
14    AS DECIMAL(10,2))                                     AS TotalSizeMB,
15    CAST(
16        SUM(a.used_pages) * 8.0 / 1024
17    AS DECIMAL(10,2))                                     AS UsedSizeMB,
18    CAST(
19        (SUM(a.total_pages) - SUM(a.used_pages)) * 8.0 / 1024
20    AS DECIMAL(10,2))                                     AS UnusedSizeMB,
21    t.create_date,
22    t.modify_date,
23    -- Count nonclustered indexes present on this heap
24    (
25        SELECT COUNT(*)
26        FROM sys.indexes AS i2
27        WHERE i2.object_id = t.object_id
28          AND i2.type > 0   -- nonclustered indexes only
29    )                                                     AS NonclusteredIndexCount,
30    -- Forwarded records via sys.dm_db_index_physical_stats (lightweight)
31    ips.forwarded_record_count                            AS ForwardedRecordCount
32FROM sys.tables AS t
33INNER JOIN sys.indexes AS i
34    ON t.object_id = i.object_id
35   AND i.type = 0                                        -- 0 = HEAP
36INNER JOIN sys.partitions AS p
37    ON i.object_id = p.object_id
38   AND i.index_id = p.index_id
39INNER JOIN sys.allocation_units AS a
40    ON p.partition_id = a.container_id
41OUTER APPLY sys.dm_db_index_physical_stats(
42    DB_ID(), t.object_id, i.index_id, NULL, 'LIMITED'
43) AS ips
44WHERE OBJECTPROPERTY(t.object_id, 'IsUserTable') = 1
45GROUP BY
46    t.schema_id,
47    t.name,
48    t.create_date,
49    t.modify_date,
50    ips.forwarded_record_count
51ORDER BY TotalSizeMB DESC;

Finding Heaps via sys.indexes type = 0

sys.indexes stores one row per index per table. The type column identifies the index structure: 0 = heap (no index), 1 = clustered B-tree, 2 = nonclustered B-tree, 3 = XML index, and so on. Joining sys.tables to sys.indexes with AND i.type = 0 returns only tables that have no clustered index — tables where the only entry in sys.indexes is the heap descriptor. The OBJECTPROPERTY(t.object_id, 'IsUserTable') = 1 filter excludes internal system tables, XML document tables, and other engine-internal objects that appear in sys.tables but are not user-created.

Size Calculation: sys.partitions and sys.allocation_units

sys.partitions has one row per partition per index — for a non-partitioned table with a heap, there is exactly one row. sys.allocation_units has one row per allocation unit type per partition: IN_ROW_DATA for normal row storage, LOB_DATA for large-object columns (varchar(max), nvarchar(max), xml, etc.), and ROW_OVERFLOW_DATA for variable-length columns that have been pushed off-row. The join ON p.partition_id = a.container_id covers all three allocation unit types in one pass. total_pages includes all pages in the extents allocated to the table, including those not yet filled; used_pages counts pages that contain at least one row. Both columns are in 8 KB pages; multiplying by 8 and dividing by 1,024 converts to megabytes.

Forwarded Record Count via sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats(database_id, object_id, index_id, partition_number, mode) returns physical statistics for an index or heap. The 'LIMITED' mode reads IAM pages and page headers without scanning leaf-level data pages, making it fast even on large tables. For heap objects (index_id = 0), the function populates forwarded_record_count — the number of forwarding stubs currently on the heap's data pages. Each forwarding stub represents a row that was updated and moved to a new page; every read that hits a stub costs one additional page I/O. OUTER APPLY is used rather than CROSS APPLY so that tables where sys.dm_db_index_physical_stats returns no rows (empty tables, brand-new heaps) still appear in the output with NULL forwarded record counts.

NonclusteredIndexCount Subquery

The correlated subquery counts nonclustered indexes on each heap by filtering sys.indexes for i2.type > 0 on the same object_id. This count is diagnostic context: a heap with zero nonclustered indexes and zero forwarded records is a benign staging table that may benefit from remaining a heap (bulk inserts into heaps are faster than into clustered tables because there is no B-tree maintenance). A heap with five nonclustered indexes and thousands of forwarded records is a strong candidate for a clustered index — adding one eliminates the RID-based row locator in every nonclustered index, removes all forwarding stubs, and organizes reads into the clustered key order.

Key Benefits and Use Cases

  • Instance-wide heap inventory for the current database — ranks heaps by size so the largest and most impactful ones surface at the top
  • Forwarded record count as a priority signal — heaps with high forwarded record counts are causing hidden I/O amplification right now; those with zero are candidates for staying as heaps
  • Nonclustered index context — shows how many nonclustered indexes are absorbing the RID-lookup penalty; more indexes mean more pain from forwarded records
  • Create-date and modify-date columns — reveal whether a heap is a long-standing design decision or a recently created table that was simply never indexed
  • Unused space tracking — large UnusedSizeMB on a heap may indicate a previously large table that was bulk-deleted; a REBUILD can reclaim that space
  • Pre-schema-review audit — run before a performance review engagement to produce a prioritized list of heap remediation candidates

Performance Considerations

  • sys.dm_db_index_physical_stats with LIMITED mode is fast — it reads IAM pages and page headers only, not leaf pages; even on tables with billions of rows, LIMITED mode completes in seconds
  • ForwardedRecordCount is only populated for heap objects — the column returns NULL or 0 for clustered and nonclustered index objects; the i.type = 0 join ensures the OUTER APPLY only targets heaps
  • The query runs per-database — sys.tables, sys.indexes, and sys.allocation_units are database-scoped catalog views; to audit heaps across multiple databases, wrap the query in a cursor or use sp_MSforeachdb
  • OBJECTPROPERTY IsUserTable is evaluated per row — on databases with thousands of tables this is not a performance concern, but it is a function call per row; alternatively filter on t.is_ms_shipped = 0 for a simpler catalog-only approach
  • GROUP BY on forwarded_record_count — if OUTER APPLY returns multiple rows per table (which can happen on partitioned tables), the GROUP BY will produce multiple rows per table; for partitioned heaps, replace with MAX(ips.forwarded_record_count) in the SELECT list and remove it from the GROUP BY

Practical Tips

  • Not all heaps need a clustered index — bulk-load staging tables, queue tables with frequent full-table-truncate patterns, and very small lookup tables often perform fine as heaps; use ForwardedRecordCount and query plan analysis to decide, not just the presence of a heap
  • Fix forwarded records with a clustered index — adding a clustered index eliminates the heap storage structure entirely; SQL Server rebuilds the table in clustered key order, all nonclustered index row locators update from RIDs to clustered keys, and all forwarding stubs disappear
  • Fix forwarded records without adding a clustered index using REBUILD — ALTER TABLE <TableName> REBUILD reclaims forwarding stubs and compacts the heap; this is appropriate for staging tables or tables where the heap design is intentional
  • After adding a clustered index, rebuild nonclustered indexes — the nonclustered index entries still carry the old RID row locators until rebuilt; rebuilding them immediately after the clustered index creation updates the row locators to the clustered key and reclaims RID-lookup overhead
  • Schedule quarterly — add this query to a monthly or quarterly review job; a heap that is benign today can accumulate hundreds of thousands of forwarded records within a few months of UPDATE-heavy workloads

Conclusion

This script from the ktaranov/sqlserver-kit community repository, extended with forwarded-record detection patterns from Brent Ozar Unlimited, provides a ranked inventory of heap tables in the current database with the diagnostic context needed to decide whether each heap warrants a clustered index. The forwarded record count is the highest-priority signal: heaps with high counts are generating hidden I/O overhead on every read that touches a forwarded row, and adding a clustered index eliminates that overhead entirely.

References

Posts in this series