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                               Listing 1: Finding Forwarded Records                              

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