Mysterious Forwarded Records


Thinking Of You

When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps.

Of course, without a clustered index, any table is a Heap.

This isn’t an argument for or against indexing temp tables, but while working with a client we came across something strange!

sp_BlitzFirst was reporting hundreds of thousands of Forwarded Records, but there were no Heaps in user databases.

When we dug in closer to what queries were doing, we found lots of places where temp tables had an insert/update pattern.

Setting Up

A close approximation of the problem part of this code looked like this:

A temp table got created, some amount of data was inserted to it, the temp table got updated with values from another table, and then there was a final select from the temp table.

Setting up SQL Query Stress to run that proc across a bunch of threads…


And then running sp_BlitzFirst for a 30 second sample:

We get these results back!

A Crapload By Any Other Name

Changing The Pattern

The reason why we warn about forwarded records is that they can cause a lot of additional random I/O. Reads need to jump around from where a row originally was, to where it lives now, and then back to continue reading rows.

In this case, it was simple enough to change the pattern to insert with the same join that performed the update.

If you ever happen across a Forwarded Fetches warning in sp_BlitzFirst, but you’re Heap-free in your user databases, they may be lurking in tempdb.

This can happen with temp tables or table variables — they both occupy space in tempdb, regardless of what someone told you about table variables being in memory only.

Thanks for reading!

Previous Post
Wanna Attend All My Classes for a Year?
Next Post
[Video] Office Hours 2018/5/30 (With Transcriptions)

7 Comments. Leave new

  • alen teplitsky
    June 1, 2018 2:02 pm

    Seen this before. Seen procedures like this literally rebuild a multi-million row table every few minutes. And I’ve seen these run on schedules. Except add at least a dozen more steps and temp tables to the example above.

    • Erik Darling
      June 1, 2018 2:29 pm

      Alen — yeah, I can’t even get mad when I see stuff like that anymore. At some point, it solved a problem for someone.

  • Graham Furner
    October 24, 2019 1:37 am

    Is it possible to determine which query/ies are causing this problem?

    • Graham – you know, that’s a great question. The only way I can think of offhand would be setting up an Extended Events session for it.

      I wouldn’t approach it that way though – it’s not a query problem, it’s an index problem. Use sp_BlitzIndex @GetAllDatabases = 1 to identify where the heaps are. It has a warning about heaps with forwarded fetches. Go rebuild those and/or put a clustered index on ’em.

  • Graham Furner
    October 24, 2019 9:58 am

    Thank you. sp_blitzindex worked a treat!

  • Luke MacDonald
    July 15, 2022 5:22 pm

    We don’t have any heaps in the database. And the fetches are in a “unknown table” in tempdb. So it is likely a similar issue to the one demonstrated above, so sp_blitzindex isn’t helpful enough. What would a trace look like to catch this. Would sp_BlitzCache @SortOrder = Memory Grant or Spills maybe lead me in the right direction?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.