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.