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.
A close approximation of the problem part of this code looked like this:
CREATE PROCEDURE dbo.YourMomsAHeap
CREATE TABLE #el_heapo
INSERT #el_heapo ( UserId, DisplayName )
SELECT TOP 1000
FROM dbo.Users AS u
WHERE EXISTS ( SELECT 1 / 0 FROM dbo.Badges AS b WHERE b.UserId = u.Id )
ORDER BY u.CreationDate ASC;
SET eh.LastBadgeName = ca.Name, eh.LastBadgeDate = ca.Date
FROM #el_heapo AS eh
CROSS APPLY ( SELECT TOP 1
FROM dbo.Badges AS b
WHERE b.UserId = eh.UserId
ORDER BY b.Date DESC ) AS ca;
FROM #el_heapo AS eh;
DROP TABLE #el_heapo;
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:
EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;
We get these results back!
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!