Rebuild the Heap Tables Script

Posted by Barac in SQL Server on Jun 9th, 2020 | 0 comments

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

ALTER TABLE TableName REBUILD;

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.

-- 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 index_id=0 AND page_count>10000 
GO

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.

-- Create Table List in TV
USE database_name
DECLARE @TableList table(id int identity(1,1), TableName varchar(255))
INSERT INTO @TableList
SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.dm_db_partition_stats st
WHERE index_id = 0 -- index_id / ID of the heap or index the partition is part of / Heap=0 
AND used_page_count >10000
ORDER BY st.row_count DESC
-- LOOP Table Names through the DMV
DECLARE @counter int
SET @counter = 1
WHILE(@counter <= (SELECT MAX(id) FROM @TableList))
BEGIN
DECLARE @colVar varchar(255)
SELECT @colVar = TableName FROM @TableList WHERE id = @counter
DECLARE @TableForwardedRecord TABLE (RowID int identity(1,1), table_name nvarchar(255), forwarded_record_count int)
INSERT into @TableForwardedRecord SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@colVar), DEFAULT, DEFAULT, 'DETAILED')
WHERE forwarded_record_count >10000 AND index_id=0 AND page_count>10000
SET @counter = @counter + 1
END
-- read table names ordered by Forwarded Record Count
SELECT * FROM @TableForwardedRecord 
ORDER BY forwarded_record_count DESC

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

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

-- Create Table List in TV
USE database_name
DECLARE @TableList table(id int identity(1,1), TableName varchar(255))
INSERT INTO @TableList
SELECT OBJECT_NAME(OBJECT_ID) TableName
FROM sys.dm_db_partition_stats st
WHERE index_id = 0 -- index_id / ID of the heap or index the partition is part of / Heap=0 
AND used_page_count >10000
ORDER BY st.row_count DESC

-- LOOP Table Names through the DMV
DECLARE @counter int
SET @counter = 1
WHILE(@counter <= (SELECT MAX(id) FROM @TableList))
BEGIN
DECLARE @colVar varchar(255)
SELECT @colVar = TableName FROM @TableList WHERE id = @counter
DECLARE @TableForwardedRecord TABLE (RowID int identity(1,1), table_name nvarchar(255), forwarded_record_count int)
INSERT into @TableForwardedRecord SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@colVar), DEFAULT, DEFAULT, 'DETAILED')
WHERE forwarded_record_count >10000 AND index_id=0 AND page_count>10000
SET @counter = @counter + 1
END

-- PRINT or EXECUTE REBUILD SQL Statement
DECLARE @fr_counter int
SET @fr_counter = 1
WHILE(@fr_counter <= (SELECT MAX(RowID) FROM @TableForwardedRecord))
BEGIN
DECLARE @tn_var varchar(255)
SELECT @tn_var = table_name FROM @TableForwardedRecord WHERE RowID = @fr_counter
DECLARE @sql varchar(max) = 'ALTER TABLE '+@tn_var+' REBUILD'
-- PRINT (@SQL) -- to print SQL statements 
EXEC (@SQL) -- to execute SQL statements 
SET @fr_counter = @fr_counter + 1
END

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

SQL Server / Forwarded Records / Rebuild the Heap Tables Script

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud