SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Cluster That Index - Part 2

By Christoffer Hedgate,

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 
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'Orders2') 
  DROP TABLE Orders2 
INTO Orders2 
FROM Orders 
ALTER TABLE Orders2 ADD BigString varchar(4000) 
CREATE NONCLUSTERED INDEX ixOrders2CustomerID ON Orders2 (CustomerID) 

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):


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) 

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:


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.
Total article views: 8541 | Views in the last 30 days: 5
Related Articles

Index Location?

Index Location?


clustered index

clustered index


cluster index or non cluster index

cluster index or non cluster index


Cluster That Index!

Quite a while back Andy wrote an article about why not using primary keys and clustered indexes was ...


Lookups on Clustered Indexes

Lookup on Clustered Indexes

sql server 7