I have a table with about 40 million rows. This table is a heap and has several non-clustered indexes. I always thought that 1 row could have only one forward fetch.
I ran sp_blitzindex from Brent Ozar to diagnose the current performance issues.
According to sp_blitzindex, this heap has over 5 billion forwarded fetches (...).
Can anyone please explain how this is possible? I am not looking for any design considerations, just an explanation on how this works. Thanks a lot! Regards, SQL_M.
Logical fragmentation.
Once upon a time: row 1 was next to row 2, was next to row 3 etc on the same page. This would have happened on initial insert.
Over time, row 2 got updated (let's say a varchar column was doubled in number of stored characters). Row 2 would have been moved to a new page to avoid shifting 39,999,998 rows "down a bit".
To counter this, pointers were created from row 1 to the row 2 new location, and then back to row 3.
Repeat over 40 million rows with no clustered index which means no way to defragment the data in disk, and you easily get to 5 billion