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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
CREATE PROCEDURE dbo.YourMomsAHeap AS BEGIN CREATE TABLE #el_heapo ( UserId INT, DisplayName NVARCHAR(40), LastBadgeName NVARCHAR(40), LastBadgeDate DATETIME ); INSERT #el_heapo ( UserId, DisplayName ) SELECT TOP 1000 u.Id, u.DisplayName 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; UPDATE eh SET eh.LastBadgeName = ca.Name, eh.LastBadgeDate = ca.Date FROM #el_heapo AS eh CROSS APPLY ( SELECT TOP 1 b.Name, b.Date FROM dbo.Badges AS b WHERE b.UserId = eh.UserId ORDER BY b.Date DESC ) AS ca; SELECT * FROM #el_heapo AS eh; DROP TABLE #el_heapo; END; |
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:
|
1 2 |
EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1; GO |
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!
7 Comments. Leave new
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.
Alen — yeah, I can’t even get mad when I see stuff like that anymore. At some point, it solved a problem for someone.
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.
Thank you. sp_blitzindex worked a treat!
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?
For questions beyond what’s in the blog post, like how to run a trace, head over to https://dba.stackexchange.com or https://sqlservercentral.com.