Executive Summary:
Forwarded records are a type of fragmentation within heap tables that can cause poor SQL Server performance. Learn how to use the sys.dm_db_index_physical_stats dynamic management view (DMV) to find forwarded records and the FixForwardedRecs stored procedure to remove them. The FixForwardedRecs stored procedure removes forwarded records without permanently changing the table structure and without unloading and reloading data in a table.

So you’ve checked all the likely suspects, but your SQL Server is still performing poorly. Before you put that capital request in for more horsepower, think about a hidden performance killer you might have missed—forwarded records. Forwarded records are a type of fragmentation that’s hard to detect and harder to resolve. By automating the discovery and resolution of forwarded records, you can eliminate this performance killer from your databases. This article walks you through how to use a stored procedure that’s designed to intelligently detect and eliminate forwarded records, as well as log the affect of their removal.

How Forwarded Records Are Created


In essence, forwarded records are “fragmentation” within a permanent or temporary heap table. (Forwarded records are found only in heap tables.) Heap tables, the most simple storage arrangement, consist of unorganized data pages. Rows are inserted into a heap wherever there happens to be space. Because a heap is unordered, the data pages aren’t linked together in any way. When a heap table is accessed for information and no clustered index is available to support requests, the table might be scanned inefficiently. The scan can be even more inefficient if forwarded records exist.

A forwarded record occurs when a heap table (either permanent or temporary) is created that includes variable length fields. When a batch is called to load data into the table, some of the variable length fields might be populated with a short value (e.g., the letters A or N). A second batch might be called to transform the data in this field into something more user friendly (e.g., A = Acceptable and N = Not Acceptable) but that is a larger value than was initially used. This is exactly what creates the forwarded records.

When a larger value is updated in a variable length field in a record, SQL Server will first try to expand the row of the existing page on the chance that there’s enough room to continue to use the original page. If that fails and SQL Server can’t find an existing page with enough room for the larger value, a new page has to be created for the record. The data is moved to the new page and is assigned a new Relative Identifier (RID). The old page now contains a forwarding pointer (forwarded record) that tells SQL Server to jump to the new RID. The new record location also has a back pointer to the old record. Forwarded records prevent non-clustered indices on heap tables from being updated with the RID of the new row. The overhead of updating indices is worse than replacing the old records with a pointer. But the drawback to forwarded records is that their presence could result in a large amount of I/O. For each forwarded record, SQL Server has to jump to the target page, then back to the original one. This is a very inefficient way to read a row, and it also wastes space.

Although forwarded records can occur on all heap tables they don’t always and you can take steps to alleviate them. For example, you could initially populate tables with dynamic columns with long values. This approach would initially set the record to a large enough size to accommodate most updates and create minimal forwarded records. Updating a dynamic column from a long value to a shorter value won’t create any forwarded records. Developers should be very aware of how temporary tables are created and populated. Often, no one is aware of the existence of forwarded records in temporary tables because the objects are destroyed when batches are completed. Web Listing 1 shows how forwarded records are created.

Finding Forwarded Records


Now that you know how forwarded records are created, let’s look at how you find forwarded records and determine if they’re causing poor performance. Use the dynamic management view (DMV) sys.dm_db_index_physical_stats, as shown in Listing 1, to find forwarded records. The results from this DMV (displayed in Table 1) show that there are now 24,142 forwarded records in FRTable. If FRTable is called in a query from a user’s web application, that user would start to experience slow response because SQL Server had to do many more reads to satisfy the request. After doing a DBCC DROPCLEANBUFFERS and a DBCC FREEPROCCACHE, a quick check of STATISTICS IO of the query “SELECT * FROM FRTable” before the forwarded records are created show that it performed 75 logical reads, 0 physical reads and 21 read-ahead reads. After the forwarded records are introduced, the same query performs 24,432 logical reads, 0 physical reads and 46 read-ahead reads. This is a 32,576 percent increase in logical reads and a 119 percent increase in read-ahead reads, which translates to much more work for SQL Server.

Just for effect, let’s update our variable length field (i.e., nvarchar(50) column) with a larger value by using the following command:

UPDATE FRTable SET Expandable = REPLICATE(N'x',40)
GO

If you check the sys.dm_db_index_physical_stats DMV again, you can see that the number of forwarded records has increased to 26,885. There are now more rows than before that have forwarded records. If you check STATISTICS IO of the query “SELECT * FROM FRTable” again you’ll find that SQL Server performed 27,332 logical reads, 0 physical reads and 262 read-ahead reads. (Don’t forget to run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each attempt.)

Defragging or rebuilding indexes doesn’t resolve forwarded record fragmentation. The following are the only three ways to remove forwarded record fragmentation from a heap table:

  • Add a permanent clustered index.
  • Temporarily add a clustered index and then remove it.
  • Move all of the data into another table (e.g., temporary table, table variable, permanent table), truncate the original table, and reload the data.

Removing Forwarded Records from a Database


In my experience, resolving forwarded records with one of the three known methods listed above has been a slow, manual process. Because I’m not a fan of manual processes, I created a stored procedure designed to do it all for you. The procedure, which uses the first two methods noted above, corrects forwarded records without permanently changing the table structure and without unloading and reloading data in a table. The stored procedure

  1. provides a dummy clustered index name and dummy auto incrementing ID column name.
  2. finds tables with forwarded records.
  3. checks for the presence of an auto incrementing ID column.
  4. creates a clustered index.
    1. If there’s an existing auto incrementing ID column, then create a dummy clustered index on the existing auto incrementing ID column. Then drop the dummy clustered index when you’re done.
    2. If there’s no existing auto incrementing ID column, then create one using the dummy auto incrementing ID column name and create the dummy clustered index on the new column. First drop the dummy clustered index and then drop the dummy auto incrementing ID column when you’re done.
  5. logs the tables with forwarded records and the quantity found before and after the clustered index is applied.

The objects created in Listing 2 are designed to be placed in an administrative database, so create a database named ADMINDB. Then create the FR_LOG table using Listing 2.

Now create the stored procedure using the code in Web Listing 2. When you execute the stored procedure, you have to supply the database name that’s going to be checked for forwarded records. Because the stored procedure is located in an administrative database and not installed into each user database, you must use three-part naming to access the correct data.

The stored procedure is called using the following command:

EXEC ADMINDB.dbo.FixForwardedRecs @dbname = 'FRTest'

The final step is to create a job that executes the FixForwardedRecs stored procedure during a maintenance period. Although we are using the new sys.dm_db_index_physical_stats DMV, it still causes some blocking and should be used during a time of low to no use.

After FixForwardedRecs has run, you can view the logged results using the command

SELECT * FROM FR_Log

which returns the results shown in Table 2. You can see that FRTable started with 26,885 forwarded records, but after having the FixForwardedRecs stored procedure run against it, the table now shows 0 forwarded records.

Let’s now look at the benefits of removing forwarded records. Web Listing 3 shows a simple example of creating a heap table with a couple of non-clustered indexes. For the purpose of this article, I used SQL Server Integration Services (SSIS) to import data from AdventureWorks.Person.Address to populate the AddressHeap table with 19,614 rows. The AddressLine2 column has mostly NULL values in it, so I used the REPLICATE function to populate the AddressLine2 column with a string containing 20 characters, as shown in the following command:

update \[AddressHeap\] set addressline2 = REPLICATE(N'x',20)

Then I updated the AddressLine2 column with a string containing 40 characters, as shown in the command

update \[AddressHeap\] set addressline2 = REPLICATE(N'x',40)

There are now more than 8,574 forwarded records on the AddressHeap table. Now you can ran the query in Listing 3 and capture the STATISTICS IO. Next, run the FixForwardedRecs stored procedure on the FRTest database and removed all forwarded records from the AddressHeap table. Then then you can capture the STATISTICS IO again using Listing 4. You’ll notice there’s a 97 percent decrease in reads on a very simple query on a relatively small table.

Improve SQL Server Performance


The script I’ve provided will automatically remove forwarded records by creating a clustered index on either an existing auto incrementing column or a new “dummy” column that’s created as part of the process. In both instances, the clustered index is removed to return the table to it’s original state. You can easily identify heap tables that might need to be redesigned to include a permanent clustered index by querying for those tables that repeatedly appear in the FRLog table. Because forwarded records are a type of fragmentation, resolving them should be part of your typical database maintenance routines. By doing so, you should see a positive return on performance—and you might even enjoy a little praise from your users.

Web Listing 1: Creating Forwarded Records

-- create a test database
CREATE DATABASE FRTest;
GO
USE FRTest;
GO
-- Create a table containing a nvarchar(50) column that has a default
-- value of one character.
CREATE TABLE \[dbo\].\[FRTable\](
        \[ID\] \[int\] IDENTITY(1,1) NOT NULL,
        \[Expandable\] \[nvarchar\](50) NULL CONSTRAINT \[DF_FRTable_Expandable\]  DEFAULT (N'a')
) ON \[PRIMARY\]
GO
-- Populate the table with the a short value
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
BEGIN TRANSACTION
WHILE @i
<b>Listing 1: Finding Forwarded Records</b>
<br><br><pre>SELECT DB_NAME(database_id) as DatabaseName,
   object_id,
    OBJECT_NAME(OBJECT_ID,database_id) as TableName,
    forwarded_record_count,
    index_type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(N'FRTest'), OBJECT_ID(N'dbo.FRTable'), NULL , NULL, 'DETAILED')
GO
Listing 2: Creating the FR_LOG Table

USE ADMINDB
GO
/*************************************************************************************/
/*  Script:  FFR_DDL.sql                                                                                   */
/*      Purpose/Description:    Creates log table used in                           */
/*                        FixForwardedRecs stored procedure                                  */
/*      Notes:  FR_LOG table is used to log forwarded record removal     */
/*          information                                                                       */
/* CHANGE HISTORY:                                                                                  */
/* Date Modified   Created By    Description of Modification                          */
/* -------------   -----------   -------------------------------------                                    */
/*                                                                                                                     */
/*************************************************************************************/

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'FR_LOG') AND type = (N'U'))
DROP TABLE FR_LOG
GO
CREATE TABLE dbo.FR_LOG (
        \[rowid\] \[INT\] IDENTITY (1, 1) NOT NULL ,
        \[dbname\] \[varchar\] (128) NULL,
        \[objectid\] \[INT\] NULL ,
        \[objectname\] \[VARCHAR\] (255) NULL ,
\[schemaname\] \[VARCHAR\] (128) NULL,
\[before_frcount\] \[INT\] NULL ,
        \[after_frcount\] \[INT\] NULL ,
        \[seconds_elapsed\] \[INT\] NULL ,
        \[datedefrag\] \[SMALLDATETIME\] NULL
) ON \[PRIMARY\]
GO
CREATE  CLUSTERED  INDEX \[CIX_FR_LOG\] ON \[dbo\].\[FR_LOG\](\[rowid\]) ON \[PRIMARY\]
GO
Web Listing 2: Creating the FixForwardedRecs Stored Procedure

USE \[ADMINDB\]
GO
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'\[dbo\].\[FixForwardedRecs\]')
AND type in (N'P', N'PC'))
DROP PROCEDURE \[dbo\].\[FixForwardedRecs\]
GO
CREATE PROCEDURE dbo.FixForwardedRecs @dbname VARCHAR(128)
AS
/*************************************************************************************/
/* Stored Procedure Name: FixForwardedRecs                                            */
/* Purpose/Description:   Defrags heap tables                                               */
/* Notes:       This should be run during down times,                                */
/*      or at the very worst, during very light use of the database               */
/* Tables:      #frwork_to_do                                                                      */
/*              FR_LOG                                                                               */
/*                 sys.columns                                                                                */
/* Procedures Called:  NONE                                                                        */
/* CHANGE HISTORY:                                                                                  */
/* Version      Date Modified   Description of Modification                         */
/* -------  -------------   -------------------------------------                                         */
/*                                                                                                                     */
/*************************************************************************************/
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @tname VARCHAR(128)
DECLARE @tname2 VARCHAR(128)
DECLARE @colname VARCHAR (128)
DECLARE @idxname VARCHAR (128)
DECLARE @schemaname VARCHAR (128)
DECLARE @idcol VARCHAR (128)
DECLARE @dbid SMALLINT
DECLARE @ddlstr1   NVARCHAR (1000)
DECLARE @ddlstr2   NVARCHAR (1000)
DECLARE @ddlstr3   NVARCHAR (1000)
DECLARE @ddlstr4   NVARCHAR (1000)
DECLARE @forrecs   INT
DECLARE @objectid  INT
DECLARE @colid  INT
DECLARE @sql NVARCHAR(2000)
DECLARE @sql2 NVARCHAR(2000)
DECLARE @sql3 NVARCHAR(2000)
DECLARE @sql4 NVARCHAR(2000)
DECLARE @timebegin DATETIME
DECLARE @timeend DATETIME
DECLARE @timeelapsed INT
DECLARE @date SMALLDATETIME

-- Create the work table.
CREATE TABLE #frwork_to_do (
        dbname varchar(128),
schemaname VARCHAR(128),
        objectid int,
        objectname varchar(128),
        forwardedrecs int,
        indextype nvarchar(60),
        IDColName varchar(128),);

SET @dbid = DB_ID(@dbname)

-- This column name needs to be one that isn’t used anywhere in the database.
SET @colname = '\[fwrdreccol\]'
-- This index name needs to be one that isn’t used anywhere in the database.
SET @idxname = '\[cix_fwdrecclean\]'

       
-- Next, find forwarded records.
SET @sql2 = 'INSERT INTO #frwork_to_do (dbname, schemaname, objectid, objectname, forwardedrecs, indextype)
SELECT DB_NAME(f.database_id),
    OBJECT_SCHEMA_NAME(f.object_id,f.database_id),
    f.object_id,
    OBJECT_NAME(f.object_id,f.database_id),
    f.forwarded_record_count,
        f.index_type_desc
FROM '+@dbname+'.sys.dm_db_index_physical_stats ('+CAST(@dbid AS VARCHAR(3))+', NULL, NULL , NULL, 'DETAILED') AS f
WHERE f.index_id = 0
AND f.forwarded_record_count > 0'
       
EXEC (@sql2);

SET @sql3 = 'UPDATE #frwork_to_do SET IDColName = COALESCE((SELECT c.name FROM '+@dbname+'.sys.columns c WHERE ObjectId = c.object_id AND c.is_identity=1),'none')'

EXEC (@sql3);

-- Declare the cursor for a list of tables with forwarded records.
DECLARE curforwardedrecs CURSOR FOR  
   SELECT DISTINCT dbname, schemaname,objectid, objectname, IDColName, forwardedrecs
   FROM #frwork_to_do
   WHERE forwardedrecs > 0

-- Open the cursor.
OPEN curforwardedrecs

FETCH NEXT FROM curforwardedrecs INTO @dbname, @schemaname, @objectid, @tablename, @idcol, @forrecs

SELECT @date = GETDATE()

-- loop through the indexes
WHILE @@FETCH_STATUS = 0
BEGIN
        SELECT @timebegin = GETDATE()
        IF @idcol = 'none'
        BEGIN
               
                -- Add brackets to names
                SET @tname = QUOTENAME(@tablename)
                SET @tname2 = QUOTENAME(@schemaname)+'.'+QUOTENAME(@tablename)
                SET @tablename = QUOTENAME(@dbname)+'.'+QUOTENAME(@schemaname)+'.'+QUOTENAME(@tablename)
                SET @dbname = QUOTENAME(@dbname)
-- Add an identify column
                SET @ddlstr1 = 'USE '+@dbname+' ALTER TABLE '+@tablename+' ADD '+@colname+' INT NOT NULL IDENTITY (1,1)'
                --print @ddlstr1
                EXEC sp_executesql @ddlstr1
                -- Create a clustered index
                SET @ddlstr2 = 'USE '+@dbname+' CREATE CLUSTERED INDEX '+@idxname+' ON '+@tablename+'('+@colname+')'
                --print @ddlstr2
                EXEC sp_executesql @ddlstr2
                --Drop clustered index
                SET @ddlstr3 = 'USE '+@dbname+' DROP INDEX '+@tname2+'.'+@idxname
                --print @ddlstr3
                EXEC sp_executesql @ddlstr3
                -- Drop the column
                SET @ddlstr4 = 'USE '+@dbname+' ALTER TABLE '+@tablename+' DROP COLUMN '+@colname
                --print @ddlstr4
                EXEC sp_executesql @ddlstr4
        END
        ELSE
        BEGIN
               
                -- Add brackets to names
                SET @tname = QUOTENAME(@tablename)
                SET @tname2 = QUOTENAME(@schemaname)+'.'+QUOTENAME(@tablename)
                SET @tablename = QUOTENAME(@dbname)+'.'+QUOTENAME(@schemaname)+'.'+QUOTENAME(@tablename)
                SET @dbname = QUOTENAME(@dbname)
                SET @idcol = QUOTENAME(@idcol)
                -- Create a clustered index
                SET @ddlstr2 = 'USE '+@dbname+' CREATE CLUSTERED INDEX '+@idxname+' ON '+@tablename+'('+@idcol+')'
                --print @ddlstr2
                EXEC sp_executesql @ddlstr2
                --Drop clustered index
                SET @ddlstr3 = 'USE '+@dbname+' DROP INDEX '+@tname2+'.'+@idxname
                --print @ddlstr3
                EXEC sp_executesql @ddlstr3
        END
        SELECT @timeend = GETDATE()

        SELECT @timeelapsed = DATEDIFF(s,@timebegin,@timeend)
       
        INSERT INTO \[ADMINDB\].\[dbo\].\[FR_LOG\](dbname, schemaname, objectid, objectname, before_frcount, seconds_elapsed, datedefrag)
        VALUES (@dbname, @schemaname, @objectid, @tname, @forrecs, @timeelapsed, @date)

        -- Get the next record
        FETCH NEXT FROM curforwardedrecs INTO @dbname, @schemaname, @objectid, @tablename, @idcol, @forrecs
       
END
-- Close and deallocate the cursor.
CLOSE curforwardedrecs
DEALLOCATE curforwardedrecs

-- Empty #frwork_to_do to get the after stats
TRUNCATE TABLE #frwork_to_do;
-- Get after stats
SET @sql2 = 'INSERT INTO #frwork_to_do (dbname, schemaname, objectid, objectname, forwardedrecs)
SELECT DB_NAME(f.database_id),
    OBJECT_SCHEMA_NAME(f.object_id,f.database_id),
    f.object_id,
    OBJECT_NAME(f.object_id,f.database_id),
    f.forwarded_record_count
FROM '+@dbname+'.sys.dm_db_index_physical_stats ('+CAST(@dbid AS VARCHAR(3))+', NULL, NULL , NULL, 'DETAILED') AS f
WHERE f.index_id = 0'

EXEC (@sql2);
-- Update the log table
UPDATE ADMINDB.dbo.FR_LOG
SET after_frcount = w.forwardedrecs
FROM ADMINDB.dbo.FR_LOG f INNER JOIN #frwork_to_do w
ON (f.objectid = w.objectid and f.schemaname = w.schemaname)
WHERE f.datedefrag = @date
AND f.dbname = @dbname;

SET NOCOUNT OFF
GO
Web Listing 3: Example of Benefit of Removing Forwarded Records

-- Create a heap table with a couple of non-clustered indexes.
USE \[ADMINDB\]
GO
CREATE TABLE \[dbo\].\[AddressHeap\](
        \[AddressID\] \[int\] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
        \[AddressLine1\] \[nvarchar\](60) NOT NULL,
        \[AddressLine2\] \[nvarchar\](60) NULL,
        \[City\] \[nvarchar\](30) NOT NULL,
        \[StateProvinceID\] \[int\] NULL,
        \[PostalCode\] \[nvarchar\](15) NOT NULL,
        \[rowguid\] \[uniqueidentifier\] ROWGUIDCOL  NULL,
        \[ModifiedDate\] \[datetime\] NOT NULL
) ON \[PRIMARY\]
GO
CREATE  INDEX \[IX_AddressHeap1\] ON \[dbo\].\[AddressHeap\](\[AddressID\]) ON \[PRIMARY\]
GO
CREATE  INDEX \[IX_AddressHeap2\] ON \[dbo\].\[AddressHeap\](\[StateProvinceID\]) ON \[PRIMARY\]
GO
Listing 3: Capturing the STATISTICS IO Before Removing Forwarded Records

set statistics io on
go
select AddressLine1, AddressLine2, City, PostalCode
from dbo.AddressHeap
where City = 'Bothell'
go

-- Stats before removing the forwarded records.
Scan count 1, logical reads 9077, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
Listing 4: Capturing the STATISTICS IO After Removing Forwarded Records

set statistics io on
go
select AddressLine1, AddressLine2, City, PostalCode
from dbo.AddressHeap
where City = 'Bothell'
go

-- Stats after removing the forwarded records.
Scan count 1, logical reads 481, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0