SQL SERVER – Finding Fragmentation in Forwarded Records

Finding Fragmentation in a heap also requires a separate check for forwarded records. I measure their effect before choosing maintenance.

A customer asked how to identify forwarded-record problems during a performance health check. The starting point is the heap, a table without a clustered index. An update that enlarges a row can move it when its original page has no room.

A forwarding pointer remains at the original location. Following that pointer can add reads, but a heap is not automatically a poor design. The workload and measured cost decide whether the forwarding matters.

Inspect one heap

-- Detailed inspection can be expensive. Set the target table first.
DECLARE @object_id int = OBJECT_ID(N'dbo.YourHeap');
IF @object_id IS NULL
    THROW 50000, 'Set dbo.YourHeap to an existing heap table before inspection.', 1;
SELECT OBJECT_SCHEMA_NAME(ps.object_id) AS schema_name,
       OBJECT_NAME(ps.object_id) AS table_name, ps.partition_number,
       ps.forwarded_record_count, ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), @object_id, 0, NULL, 'DETAILED') AS ps
WHERE ps.alloc_unit_type_desc = 'IN_ROW_DATA';

I scope the scan to the table being investigated. Replace the name deliberately and check the required permissions. DETAILED can inspect many pages, so schedule it for the table size and workload.

The existing controlled demonstration

The scratch heap recorded zero forwarded records before row growth and 648 afterward. A heap rebuild returned the count to zero. Those counts belong to this test. They aren’t thresholds for another table.

A heap rebuild can remove forwarding, but it consumes resources and affects access while it runs. Another option is a suitable clustered index when it fits the design. Neither choice follows automatically from a nonzero counter.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – What is Forwarded Records and How to Fix Them?
• Pinal Dave on YouTube

A forwarded-record count is not a maintenance order, it is evidence to compare with the heap workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.