SQL Server / Forwarded Records / Rebuild the Heap Tables Script

I was working with a customer having DB with more than 100K HEAP tables. Number of Forwarded Records on some tables were huge. They are using Ola Hallengren‘s scripts to maintain non-clustered indexes on those tables, but rebuilding those indexes do not fix the forwarded records on those HEAP tables.

You actually need to rebuild the HEAP

Or to add a clustered index to the table (permanent fix).

They are in the middle of the remodeling their DB so adding clustered indexes on those HEAP tables was not an option.

What are Forwarded Records?
There are so many detailed explanations about Forwarded records available on the internet, for that reason I will not go too deep explaining what forwarded records are.

I simple terms when you update the row within the HEAP table and if that particular row cannot accommodate the new data on the same data page due to its size, SQL Server will move the row from the original page to the new page and leaved a pointer (forwarded record) pointing at the new data location.

This can cause significant performance impact on the heap table due to SQL Server needs to read more pages than required.

Easy way to find forwarded records is to run following script across whole database. 

More about how to use sys.dm_db_index_physical_stats dmv you can find here.

I used the WHERE clause since I want to run this script just for a heap tables, and only tables with 10000 and more pages, but you can adjust that to your needs. 

Bear in mind, if you have more than 100K tables this may be a really slow process. 

Since I did not want to run sys.dm_db_index_physical_stats against all 100K tables, to query the number of forwarded records I used table variable to create table name list and only then run those table names through physical stats DMV.

We have a list of tables with largest number of Forwarded Records. 

Now, I needed a script which will rebuild these tables automatically. 

Similar Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *