Find Tables with Forwarded Records

A while ago I had read about the hidden threat of forwarded records.  These silent killers are like storm troopers in the bush waiting to get you when you least expect it.

And then the other day I saw a T-SQL Tuesday post by Sankar Reddy on (blog – twitter) on determining if your SQL Server is affected by forward records.  His post details what forwarded records are and how to repro and determine if they exist on a table.

Knowing about forwarded, I’ve monitored performance counters on these at clients throughout my engagements.  The performance counters can tell me if there is any performance impact being measured regarding forwarded records.  But what they can’t tell is what tables are causing these issues to occur.

So to that end, the following script is something I cooked up a while back to allow myself to check all of the heaps in a database and determine the count of forwarded records on those tables.  It uses a CURSOR, but that’s only because sys.dm_db_index_physical_stats doesn’t allow APPLY joins to it.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

IF OBJECT_ID('tempdb..#HeapList') IS NOT NULL
    DROP TABLE #HeapList

CREATE TABLE #HeapList
    (
    object_name sysname
    ,page_count int
    ,avg_page_space_used_in_percent float
    ,record_count int
    ,forwarded_record_count int
    )

DECLARE HEAP_CURS CURSOR FOR
    SELECT object_id
    FROM sys.indexes i
    WHERE index_id = 0

DECLARE @IndexID int

OPEN HEAP_CURS
FETCH NEXT FROM HEAP_CURS INTO @IndexID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #HeapList
    SELECT object_name(object_id) as ObjectName
        ,page_count
        ,avg_page_space_used_in_percent
        ,record_count
        ,forwarded_record_count
    FROM sys.dm_db_index_physical_stats (db_id(), @IndexID,  0, null,  'DETAILED'); 

    FETCH NEXT FROM HEAP_CURS INTO @IndexID
END

CLOSE HEAP_CURS
DEALLOCATE HEAP_CURS

SELECT *
FROM #HeapList
WHERE forwarded_record_count > 0
ORDER BY 1