sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Fixing Forwarded Records

Issue 7 Number 4 April 2015

Forwarded records often cause query performance that is hard to understand and thus harder to fix. The indexing may be correct. There may be no problem with memory, disk or CPU. the query may be correctly written. It seems to you that this query should be lightning fast, but it is plodding along for about 10 seconds before completing.

forwarded records could be your problem.

What is a Forwarded Record?

Forwarded records only occur on heap tables (tables that have no clustered index). They occur when a row in the table exceeds the maximum size allowed for rows in SQL Server (8060 bytes). This occurs when varchar columns in the table are updated with a larger value. The update may make the total size of the row data larger than is permitted. When that happens, the data is moved to a new page and a pointer is left in its place.

When the heap table is scanned, following these pointers slows the operation down significantly. It is similar to index page splitting causing index fragmentation and query slowness.

How do I know if I have Forwarded Records?

Execute this query in the database in question:

  • SELECT OBJECT_NAME(stats.object_id) as [Table],
  • idx.name as [Index],
  • stats.index_type_desc,
  • stats.page_count,
  • stats.avg_fragmentation_in_percent,
  • stats.forwarded_record_count

  • FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS stats
  • INNER JOIN sys.indexes AS idx
  • ON stats.OBJECT_ID = idx.OBJECT_ID
  • AND stats.index_id = idx.index_id
  • WHERE forwarded_record_count > 0

This will show you all the tables in the database that have any forwarded records, the number of forwarded records and the size of the table.

How do I fix it?

Easy, create a clustered index on the affected tables. If you are using SQL 2008r2 or later you could also use the ALTER TABLE...REBUILD command to defrag the forwarded records but I don't know why anyone would do that. The clustered index is a permanent fix. the ALTER command would have to be repeated periodically.

If you found this newsletter useful, please tweet it to friends.

Call 877-201-3428 toll-free

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Performance Effects of forwarded records in SQL Server

Elsewhere on this site:

 
Call SQL Consulting
 
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator