I have a temp table which I update many times based on joins (each time almost all the rows). I have kept it as a heap since if I don't get an inner loop join then after each update I get a sort in the execution plan. And also the other tables which I join on need to have a unique index if I want to get the correct join (i.e. so I don't get a sort after the join). This answer helps explain the problem: Updating a table efficiently using JOIN
This was just an explanation for why the table is a heap.
Now my question is: how much do these forwarded records hurt the performance of my SP? Say I have 10 updates and with each update I get more forwarded records. Should I rebuild the heap temp table after say five updates? How can I figure out how many times to rebuild the heap?
So basically my question is how to deal with a table that gets updated many times and with each update basically all rows for a particular column get updated. This is a reporting query.
If I keep the table has a heap I get extra IO and if I create a clustered index I need to get all the statements to do an inner loop join and even if I achieve this I still will get page splits…
I have not tried this yet but I am thinking to change the string columns to fixed length columns in order to avoid forwarded records, but that seems to increase the IO also, at least at first…
Best Answer
The question of when does forwarded records hurt a particular query's performance is too broad to answer, and will vary depending on what the query is actually doing.
Nowhere in the DBA.StackExchange post you linked recommends using a Heap table, rather it shows how to make use of an indexed table by forcing alternative execution plans with proper query hinting to solve the performance problem, when applicable. If you were previously experiencing performance issues that matched the question of that post, I recommend taking up Paul White's advice, but in either case the recommendation would be to index your table, especially if you're joining to it frequently.
Alternatively, Tara Kizer has a great article on How To Fix Forwarded Records which essentially ends with the suggestions of either a temporary fix by rebuilding the table, or a permanent fix by adding a clustered index to it.
And if you're still deadset on keeping it as a Heap then to directly answer your question of "when" to rebuild the table, it generally is recommended to do so based on the Heap fragmentation. That's a more easily measurable number to use to determine when's the right time to rebuild your table. Some people choose to rebuild after 20% fragmentation, others choose 50%, some wait until the table is mostly fragmented, it just depends on your situation and the particular queries you're running against the Heap table. But you can gather more information about Heap fragmentation and rebuilding the table in SQL Server Heaps, and Their Fragmentation.