Share via

Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)

  • 11/04/2013

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: https://www.sqlskills.com/blogs/paul/forwarding-and-forwarded-records-and-the-back-pointer-size/

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:

https://blogs.msdn.com/b/john_daskalakis/archive/2013/10/07/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-1-how-to-collect-a-detailed-perfmon-trace.aspx

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.