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