A large heap is being updated

What this check looks for

Across every online, writeable user database, tables that have no clustered index (index_id = 0), with more than 100,000 rows, that have taken more than 1,000 updates according to sys.dm_db_index_usage_stats.

Both conditions matter. The check is deliberately not reading sys.dm_db_index_physical_stats, because getting the forwarded record count out of it requires a DETAILED scan, and that reads the whole table. On a large heap that is an expensive thing for a health scan to do, so the check infers the risk from size and update volume instead, and leaves the confirmation to you.

Why it matters

A heap that is only inserted into and read is perfectly fine. Staging tables, log tables and append-only landing tables are legitimate heaps and there is no reason to add a clustered index to one. This check does not fire on those, because they are not updated.

A heap that is updated accumulates forwarded records. When an update makes a row longer and it no longer fits where it was, SQL Server moves the row to a new page and leaves a pointer behind. Every read that arrives via the old location follows the pointer, so every forwarded record costs two page reads instead of one, and they accumulate forever because nothing removes them in normal operation.

What makes this one hard to find is that none of the usual tools show it:

• There is no clustered index to reorganize, so the nightly index maintenance job does nothing for this table. ALTER INDEX ... REORGANIZE has nothing to work on.
• Fragmentation reports are built around indexes, so the table does not appear on them.
• The execution plan looks the same as it always did. A table scan is still a table scan.
• The row count has not changed dramatically and the table size grows only slowly.

The table simply gets slower over time, with nothing to point at. It is one of the few performance problems that produces no signal anywhere until somebody goes looking for forwarded records specifically.

How to confirm it yourself

Find the heaps and their update volume, cheaply:

SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
       t.[name]                          AS [table_name],
       SUM(ps.[row_count])               AS [rows],
       ISNULL(MAX(us.[user_updates]), 0) AS [updates_since_restart],
       ISNULL(MAX(us.[user_seeks]) + MAX(us.[user_scans]) + MAX(us.[user_lookups]), 0) AS [reads]
  FROM sys.tables AS t WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK)
         ON i.[object_id] = t.[object_id]
 INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
         ON ps.[object_id] = i.[object_id]
        AND ps.[index_id]  = i.[index_id]
  LEFT JOIN sys.dm_db_index_usage_stats AS us WITH (NOLOCK)
         ON us.[object_id]   = i.[object_id]
        AND us.[index_id]    = i.[index_id]
        AND us.[database_id] = DB_ID()
 WHERE i.[index_id] = 0
 GROUP BY t.[object_id], t.[name]
HAVING SUM(ps.[row_count]) > 100000
   AND ISNULL(MAX(us.[user_updates]), 0) > 1000
 ORDER BY [updates_since_restart] DESC;

Then confirm the forwarded records for one named table. This reads the whole table, so run it on one table at a time and not during a busy period:

SELECT OBJECT_NAME(ips.[object_id]) AS [table_name],
       ips.[forwarded_record_count],
       ips.[record_count],
       ips.[avg_page_space_used_in_percent],
       ips.[page_count]
  FROM sys.dm_db_index_physical_stats(
         DB_ID(), OBJECT_ID('dbo.YourTable'), 0, NULL, 'DETAILED') AS ips;

A forwarded_record_count that is a meaningful fraction of record_count is the confirmation.

How to fix it

Two options, and the first is usually right.

Add a clustered index. This is the real fix. Most tables benefit from one, and the absence of one is usually an accident rather than a decision, often a table created by an import wizard or a migration that never got a primary key. A narrow, ever increasing key is the usual choice.

CREATE CLUSTERED INDEX [CX_YourTable] ON [dbo].[YourTable] ([YourKeyColumn]);

Be aware this rewrites every nonclustered index on the table, because their row locators change from a physical address to the clustered key. On a large table, plan it.

Rebuild the heap, where a clustered index genuinely is not wanted:

ALTER TABLE [dbo].[YourTable] REBUILD;

This removes the forwarded records and compacts the pages. It is available from SQL Server 2008 onwards and it is the only supported way to clear forwarded records from a heap. It is offline in Standard edition.

The rebuild is a treatment, not a cure. If the table is still updated the same way, the forwarded records come back, so the rebuild has to be scheduled. That maintenance burden is itself a good argument for the clustered index.

A third option worth considering: if the updates are what make rows longer, look at whether they need to. Updating a variable length column from empty to a value is what creates forwarding. Giving the column a default and writing the real value on insert avoids the growth entirely.

How long it takes

About two hours, mostly deciding the clustered index key and scheduling the change. The rebuild option is quicker to apply and comes back.

Related reports

Related checks

Frequently asked questions

Are all heaps bad? No. A heap that is inserted into and read is fine, and for a pure staging table it is often the best choice. It is updates that cause the problem, which is why this check requires both size and update volume.

Why not just read the forwarded record count? Because getting it requires a DETAILED scan, which reads the entire table. That is too expensive for a scan that runs across the whole instance, so the check finds the candidates cheaply and hands you the expensive query to run on one table.

The updates count looks low. sys.dm_db_index_usage_stats resets at every restart. On a recently restarted instance the numbers understate the real volume, which means this check under-reports rather than over- reports.

Will a nonclustered index help? It helps the queries that can use it, but it does nothing about forwarded records. Those are a property of the heap itself.