Forwarded records: a concept specific to SQL Server’s heap tables. These can have a big impact on performance, especially for update-heavy workloads.

What Are Forwarded Records?

In SQL Server, a forwarded record occurs when:

• A row in a heap table (a table without a clustered index) is updated.
• The new row no longer fits in its original location due to size increase.
• SQL Server moves the row to a new page and leaves a forwarding pointer behind.

This pointer links the original location to the new one, creating a performance overhead.

Why They Matter

Forwarded records can:

• Slow down read operations, especially scans.
• Increase logical reads and I/O.
• Cause fragmentation in heap tables.
• Make updates and deletes more expensive.

How to Detect Them

Use sys.dm_db_index_physical_stats to find forwarded records:

SELECT OBJECT_NAME(object_id) AS TableName,
       forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE forwarded_record_count > 0;

How to Fix Them

You can eliminate forwarded records by:

\1. Rebuilding the heap:

ALTER TABLE YourTable REBUILD;

\2. Adding a clustered index:

CREATE CLUSTERED INDEX IX_YourTable_Column ON YourTable(Column);

This converts the heap into a clustered table, which prevents forwarded records from occurring in the future.

Best Practices

• Avoid heap tables for update-heavy workloads.
• Monitor forwarded records regularly.
• Rebuild heaps during maintenance windows if needed.

From my SQL Server DBA handbook.