Specialized Performance Troubleshooting (Part 1: How to troubleshoot Forwarded Records)
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:
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.