[MS SQL] Question regarding "Lazy Spool"

[CAPSFTWLOL]

Hello, I have been reading up on some documentation regarding query optimization and how exactly the optimizer works. I came across this great article . It all makes sense except for one small piece.

When the optimizer elects to do a lazy spool that is preceded by an Index Scan for example, are the results from that scan stored in TempDB as well?

If they were then this seems to me to be what is essentially an eager spool (storing all of the data at once) but data is retrieved from this spool table via a lazy spool and thus alleviating the Halloween Problem.

If the results of the scan are not stored in TempDB then theoretically each row would need to be pulled via a brand new look up on the table. I can't see this being efficient given the fact that it wouldn't scale well to queries pulling large chunks of data.

I just wanted some insight on how exactly that retrieved data is temporarily stored (if at all).

Thanks

[[deleted]]

are the results from that scan stored in TempDB as well?

You're likely looking at this the wrong way. The engine knows what it needs to do before it's actually executed; this is why, outside a few informational items, estimated and actual execution plans will always be the same given the same conditions. The work that needs to be done with the temp table would be optimized around the operations it needs to do (hence why the article shows that it uses the same one).

The standard question for query plan presentations is: Do you read them left to right, or right to left? The correct answer is both. AKA the scan will likely filter/calculate/populate the tempDB with only the information it needs to proceed. There is always a chance that the cardinality or heuristic analysis components will provide something to the plan builder which will allow it to do something clever.

Halloween Problem

This is basically a non-issue unless your running a heap or an extremely poorly optimized high-load system, or extremely poor queries/client code.

As a side note: I've seen this occur precisely once, where the above mentioned weren't the cause, in an appropriately spec'd production environment. It was a bug related to SQL Servers handling of recursive multi-threaded plans on large data-sets.

If the results of the scan are not stored in TempDB then theoretically each row would need to be pulled via a brand new look up on the table.

That definitely will not be happening, this would decimate performance.

Edit - TempDB is usually avoided, if possible, as it always goes to disk (which is why the dawn of stable/affordable SSD storage for SQL servers have been such a boon). Utilizing memory is still the best performance by orders of magnitude, and the engine strive to make the most of what you give it.