4

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.

trincot
  • 317,000
  • 35
  • 244
  • 286
SQL_M
  • 2,455
  • 2
  • 16
  • 30
  • The article used as a source by a now-deleted answer isn't a bad place to start - http://sqlmag.com/stored-procedures/removing-forwarded-records-heap-tables - each variable length column in a row can have a forward fetch. – Ed Harper Mar 02 '17 at 12:41
  • Thanks Ed, I will read the article. – SQL_M Mar 02 '17 at 12:54

1 Answers1

4

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

I para-phrased Brent

gbn
  • 422,506
  • 82
  • 585
  • 676