Another way to get Forward Records in SQL Server

Sankar Reddy had a nice blog awhile back on forward records, and there are some other good ones out on the web about the subject as well.  The first time that I read about this topic was back in 2001 or 2002 in Ken England’s Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, a really good book back then, and even today still contains some very relevant information (like the chapter where he talks about…forwarded records :)).  While there is much out on the web, as Sankar points out, there is no need to rehash or regurgitate the background info; however, just to make sure everyone is down with what we’re talking about, let’s review: a forwarded record occurs in a heap table (a table with no clustered index) as a result of a change made to the table schema or table data after its original creation.  Because the change can’t be handled by the way the table data is laid down from either the initial or some previous load, a pointer (RID) to a new page for “overflow” data is created, specifically for a varchar column.  This can cause more reads and IO due to this type of traversal whilst performing DML on the table.  Not good in other words.  Slower queries, more CPU required, and more disk I/O can be seen with a table containing forwarded records to one that does not.  The best solution to avoid this scenario would be to, of course, assign a clustered index on the table and be done with it.  If you can’t, you have to remove this type of fragmentation from the table.  There are various scripts out there to do this, so check Google! for one to remove those records.

In Sankar’s post, he does an ALTER statement and adds a column to create his forward records. My post today is to remind that while adding a column is certainly a way to create these forward records, I believe that they most often occur by issuing an UPDATE to a variable-length column after the initial data load to the table. Let’s check it out quickly – run the following:

USE master
GO
CREATE DATABASE Test
GO
USE Test
DROP TABLE HeapWForward
GO
CREATE TABLE HeapWForward
    (id int
    ,string varchar (400)     
    )
GO
PRINT 'A heap table has been created. Wooo hooo!'
GO

INSERT INTO HeapWForward
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.id) AS Id,   ' '
FROM sys.syscolumns A, sys.syscolumns B 
GO
SELECT forwarded_record_count AS ForwardRecordCount_Before
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward')
                        ,NULL,NULL,'Detailed') as a
GO 
UPDATE HeapWForward
SET string    = REPLICATE('a',  400)
GO
SELECT forwarded_record_count AS ForwardRecordCount_After
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'HeapWForward')
                        ,NULL,NULL,'Detailed') as a
GO

Notice that lots of forward records appear after the update. Sure, an ALTER TABLE with ADD column will produce them, but in online and batch SQL processing, you’re more likely to get them from something such as the above.  For instance, if you use temporary tables and load data and subsequently UPDATE an existing column, you will get forward records.

By the way, Microsoft says that read ahead reads will actually decrease with fragmentation, since the read ahead manager can’t read in larger chunks. The same goes for this type of fragmentation as well.  Read-aheads are desirable because this is the mechanism to cache pages and anticipate what the query will need in order to finish in an optimal time frame. 

A perfmon counter to use to test this is the Avg Disk Bytes Read:

We see this in perfmon very clearly when running a SELECT full scan.  Here’s a scan issued to the table with the data pre-populated - lots of  bytes, and lots of read-aheads:

Here’s the same query with the forward records. Notice the great decrease in the Avg. Disk Bytes/Read counter.

Checking STATISTICS IO, in the below image, the first result is from a scan of all records, the second a full scan with forwarded records, and the third with the UPDATE HeapWForward already issued (pre-populated). Notice the read-aheads are much greater with the table already updated; the table with the forward records has many more logical reads, physical reads, with fewer read-aheads due to the fragmentation present.  As important, experiment with sys.dm_db_index_operational_stats; you’ll find that forward records, because SQL Server must traverse more pages, will also cause a greater accumulation of row locks and page locks, and this can be seen as well very clearly. Pretty cool, eh?

Thanks for reading,

Lee Everest

----------------------------

I know. We really didn’t need another forward record post, did we?

Sankar’s blog post - http://sankarreddy.com/2010/03/how-can-i-tell-if-a-sql-server-system-is-affected-by-forwarded-records/

Microsoft SQL Server 2000 Index Defragmentation Best Practices - http://msdn.microsoft.com/en-us/library/cc966523.aspx

Microsoft: http://msdn.microsoft.com/en-us/library/ms191475.aspx