SQL SERVER – Finding Fragmentation in Forwarded Records

During the recent SQL Server Comprehensive Database Performance Health Check, I was asked by my client if there any way to know if there is any way of Finding Fragmentation in Forwarded Records. Yes, there is a way to do it and I have blogged about it over here: SQL SERVER – What is Forwarded Records and How to Fix Them?

Forwarded Records

Forwarded Records are the data row in a heap that has moved from the original page to the new page. A heap table is a table without a clustered index. When a user updates a column with a larger size of data, it is possible that a particular field now can’t fit on the same data page leaving a forwarded record of that data.

When SQL Server scans the heap to read the necessary data, they often encounter forwarding record pointers which usually exist on the different page leading SQL Server to read more pages than required while dealing with the heap. This is the reason, often performance of the heap has been very bad.

Finding Fragmentation

Here is the script which you can use to find the fragmentation of the heap tables.

-- Check Forwarded Record Count
SELECT OBJECT_NAME(object_id) AS table_name,
forwarded_record_count,
avg_fragmentation_in_percent,
page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED')
WHERE forwarded_record_count is NOT NULL
GO

Well, that’s it for today. If you want to learn how to remove the fragmentation issue from the heap, I strongly suggest that you read this blog post: SQL SERVER – What is Forwarded Records and How to Fix Them?

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)