This week I will discuss how to troubleshoot Forwarded Records issues.


What are Forwarded Records?

If you have some time, you can read the full story at Paul Randal’s blog post:

If not, I will tell you the short story: "They are a performance overhead that can occur when you insert rows into a HEAP table (a table that has no clustered index). And sometimes it can really hurt the server performance."


How do I know if I am affected by this issue?

The most commonmethod to detect Forwarded Records is by collecting a Perfmon trace:

If you see that the number of Forwarded Records/sec is 10% or more of the Batch Requests/sec, then these Forwarded Records are likely impacting the server’s performance.


How do I resolve this issue?

It is actually a very straightforward process.

You can use this query to identify the tables that have the forwarded records:

  SELECT DB_NAME(database_id) AS database_name, OBJECT_NAME(OBJECT_ID) AS OBJECT_NAME, forwarded_fetch_count

  FROM sys.dm_db_index_operational_stats (DB_ID('database_name'), NULL, NULL, NULL)

  order by forwarded_fetch_count desc

And also you can use this query to identify which tables of your database are HEAPs:

  SELECT SCHEMA_NAME(o.schema_id) AS [schema],object_name(i.object_id ) AS [table],p.rows FROM sys.indexes I

  INNER JOIN sys.objects o ON i.object_id = o.object_id

  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id

  WHERE i.type_desc = 'HEAP'

  ORDER BY rows desc

If from the above queries you can identify any HEAPs that also have forwarded records, you should create a clustered index on these HEAPs to avoid this issue altogether.


Next week I will discuss how to quickly and efficiently troubleshoot memory issues.