I have previously discussed the issue of forward-pointers in the article

Cluster that index!. I described what forward-pointers are and how they are

created by SQL Server. I also supplied a script that showed the effect of

forward-pointers, but I did not discuss how to check for the existence of

forward-pointers and how to remove them. This article will discuss this.

Recap of problem

Forward-pointers are created by SQL Server to avoid making transactions longer

than necessary. As described in the article mentioned above, the leaf level

pages of non-clustered indexes contain pointers to the data that is indexed by

them. If the table that the index is created on has a clustered index created

for it, these 'pointers' are bookmark lookup values, each one containing a key

value to look up in the clustered index. If the table does not have a clustered

index, i.e. a heap table, these pointers point to the actual physical location

of the rows in the data files. The problem is that data rows sometimes need to

be moved to another data page. One reason is when the value of a variable length

column is changed and the row no longer fits into the page where it is located.

Now SQL Server must either change all of the pointers for this row (in all

non-clustered indexes for the table) to it's new location, or it can use

forward-pointers. A forward-pointer is simply a pointer left in the original

location of the row, pointing to the new location. This way no indexes need to

be updated, SQL Server just follows the forward-pointer to the new location of

the row when it needs to fetch it. As I said, instead of updating the pointers

in all non-clustered indexes each time a row is moved, SQL Server uses

forward-pointers to avoid making the transactions longer than necessary.

The problem with forward-pointers is that they can create a lot of extra I/O.

When scanning a heap table containing forward-pointers, SQL Server needs two

extra page reads for every forward-pointer, which in extreme situations might be

very cumbersome. A

script that showed this was supplied in the other article.

Checking for

forward-pointers

There are two ways in SQL Server to check for the existence of

forward-pointers in a heap table. Before we view how to do this, use the

following code snippet to create a table to use later:

USE Northwind 
GO 
 
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Orders2') 
  DROP TABLE Orders2 
GO 
 
SELECT * 
INTO Orders2 
FROM Orders 
GO 
 
ALTER TABLE Orders2 ADD BigString varchar(4000) 
GO 
 
CREATE NONCLUSTERED INDEX ixOrders2CustomerID ON Orders2 (CustomerID) 
GO 

The first way to check for forward-pointers is by using DBCC SHOWCONTIG, and

supplying the option WITH TABLERESULTS. This option adds extra columns to the

output of DBCC SHOWCONTIG, one of them is called ForwardedRecords. This column

shows how many records (rows) of the table that have been moved to a new

location and have a forward-pointer left in their original location. The syntax

to run this is shown below, where @id represents the object id of the table you

want to check (use OBJECT_ID() to retrieve this id):

DBCC SHOWCONTIG (@id, 0) WITH TABLERESULTS 

At the moment the result of this command should show that Orders2 has 0

forwarded records. However, if you run the code below, the result will be very

different:

UPDATE Orders2 SET BigString = REPLICATE('-', 4000) 
DBCC SHOWCONTIG (@id, 0) WITH TABLERESULTS

This time the table contains 810 forwarded records. The other way to check

for forwarded rows is by running DBCC CHECKTABLE for the table you want to

check. To have CHECKTABLE return info about forwarded records trace flag 2509

must be activated, so the following code will return info about forward-pointers

in Orders2:

DBCC TRACEON (2509) 
DBCC CHECKTABLE ('Orders2') 

Removing forward-pointers from a table

In the article about clustered indexes I said that there is no way in SQL Server

to remove forward-pointers. Although there is no system procedure or DBCC

command to simply remove them, there is actually a way to get rid of them. It is

a very simple and effective solution, but for large tables it might take some

time. Simply create a clustered index for the table, which will update all

leaf-level pages of non-clustered indexes to contain bookmark lookup values for

the clustered index instead of physical file pointers. Since the leaf-level

pages of a clustered index contains the actual data rows in sorted order (as

described in the article about clustered indexes), the data rows will need to be

resorted and moved, at the same time removing the forward-pointers.

If you don't want to keep the clustered index, just drop it and the

non-clustered indexes leaf-levels will be changed back into pointers to the

physical location of the data rows, however this time they will point to the

actual location of the rows.

As a final note, when a database is shrunk, the bookmarks of non-clustered

indexes are reassigned and therefore any forward-pointers located on pages that

are removed by the shrinking process are removed.