Removing Forwarded Records from Heap Tables

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.