Forwarding Pointers in Heaps

When no clustered index is defined on a table, that table is said to be a Heap. Heaps are not ideal when it comes to performance but there are lots of instances where you have heaps.  

Operations on heaps

INSERT –  New rows can be placed in the first available page with sufficient space. So whenever a new row is inserted most probably it will be added to the last page.

UPDATE – Rows can either remain on the same page if it fits in the page after the update, if not it will be removed from the current page and placed on the first available page with sufficient space. This is where forwarding pointers comes into the picture. We will be discussing this later.

DELETE – Data is not overwritten, space is just flagged as available for reuse.

SELECT – The entire table will need to be read for most queries (ie a table scan is performed) if no indexes are available

Forwarding Pointers

There can be non-clustered indexes in the Heap. So in case a record has to move to new page, at the old location of the row it will leave a forwarding pointer. This mechanism is to stop the updating of non-clustered indexes as it can still point to the old location. So the modification is less costly than if all non-clustered indexes needed to reflect the row move.

Let us try this with sample data as shown below.

CREATE TABLE Test
(ID BIGINT IDENTITY(1,1),
Description VARCHAR(200),
Type int,
sys_datetime datetime default getdate()
)
CREATE INDEX idx_sys_Datetime ON Test(sys_datetime)
CREATE INDEX idx_type ON Test(Type)
DECLARE @i int =0
WHILE @i < 100
BEGIN
   INSERT INTO Test
   (Description,Type)
   VALUES  ( 'Sample Data for ' + CAST(@i as varchar(10)),
   RAND(1) * 100 )
   SET @i = @I + 1
END

After this let us check some information about the Heap using following query.

SELECT
    OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.index_type_desc,
    ps.page_count,
    ps.avg_fragmentation_in_percent,
    ps.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    ON ps.OBJECT_ID = i.OBJECT_ID  AND ps.index_id = i.index_id
WHERE OBJECT_NAME(ps.object_id) = 'test'

You can see there 0 forwarded records for the HEAP.

Then let us add a column so that rows will not be fitting on a page and forces SQL Server to move those rows to another page :

ALTER TABLE Test ALTER COLUMN Description char(400);

Thus, you now you have 85 forwarded records.

Forwarding pointers are common performance problem in Heaps. SQL Server 2008 introduced ALTER TABLE ... REBUILD which removes all forwarding pointers and performs a rebuild:

ALTER TABLE test REBUILD 

Now let us re-examine the forwarded recount again which will be back to zero.

Multiple forwarding pointers

Let us assume a row has to be moved another page and it has to be moved again. Technically there can be two options for this:

1. Another forwarding pointer is created and updated in the location where the row was prior to the move.
2. Simply update where the row was originally was.

Whatever the option you select, indexes need not to be updated. SQL Server by default uses the second method which is updating the original forwarding pointer.  By using this mechanism, performance will not be affected by having to follow a chain of forwarding pointers.

]]>