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…

STRASS

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)

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

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

      Reply

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.

Menu
{"cart_token":"","hash":"","cart_data":""}