2

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...

3
  • 1
    I doubt that it is not even possible to approach a generalized answer here. Way too many moving parts. And we don't have access to your data so we can play with settings and see which is more efficient. Just one example is how much fits in memory vs physical access. If the records that are forwarded fits in memory, then you might not pay much for the "fragmentation". OTOH, since you jump to somewhere else every time you reach a forwarded record and have to read that page, you can end up in being able to fit less data in memory, end up in physicals I/O scattered all across the place. Mar 15, 2021 at 13:38
  • Above is just one aspect, and you then have to weigh that to the alternative plans and in what way they are less efficient to your current setup. And, there's also the possibility that you could improve on the plans with clustered index - we can't say that since we don't have a repro or the plan. So, you probably have to do some testing yourself, as much as you can prioritize and see what is best for you and your situation, in the end. Mar 15, 2021 at 13:41
  • 1
    I'd say the first thing to think about is your updates: can they be merged into fewer, or even one single, update Mar 17, 2021 at 2:21

1 Answer 1

2

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.

6
  • The problem is that I am joining by different columns and if I don't get them all right so that I avoid a sort then the sort is too slow. I have many update statements. My testing shows that keeping it as a heap is faster unless I get the exact execution plan without a sort... But thanks. I wait a bit to see if others have an answer...
    – xhr489
    Mar 15, 2021 at 22:28
  • I mean the problem is mostly indexing the other tables, which need to have a unique index for it to work (i.e. with no sort after the join). I mean if it is only running once a day why bother to make the perfect indexes? (The last sentence is a question and not a statement)
    – xhr489
    Mar 15, 2021 at 22:32
  • @xhr489 The name of the game isn't finding the perfect index, as such a thing often doesn't exist, but you should be able to come up with a few very good indexes to support your needs. A unique index doesn't guarantee no sorting and lack thereof doesn't guarantee poor performance on those indexes. I find it hard to believe if sorting is your issue, that a Heap solved that issue. I couldn't advise you much more specifically though without comparing execution plans, seeing the queries, and knowing how much data your tables have. But I can say is I've worked with tables in the...
    – J.D.
    Mar 15, 2021 at 23:33
  • ...10s of billions of rows, multi-terabytes big, and proper indexing allowed querying on them to be near instantaneous on very modest hardware (less provisioned than my laptop, in some cases).
    – J.D.
    Mar 15, 2021 at 23:34
  • Only the last part of your answer is relevant to my question. Rest is general advice unrelated to my question. Please edit your answer. Also my question is about measuring the impact of forwarded records. Also heaps are faster for inserts and using them correctly can improve etl jobs.
    – xhr489
    Mar 17, 2021 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.