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.




Array

No comments yet... Be the first to leave a reply!