What is the lazy spool doing in this plan?
By Jonesboy21 16 asked May 07, 2013 at 03:41 PM
The purpose of the lazy spool is to cache the most recent result set from the inner side of the nested loops join. The idea is that if the next iteration of the nested loops join uses the same correlated parameters, the spool can 'rewind' - replay the results from the prior execution. This saves the cost of evaluating the inner side subtree, at the cost of caching the result in a work table.
The specific arrangement in your query plan is known as a 'performance spool'. The optimizer introduces a Sort on the outer side of the nested loops join to ensure that if there are correlated parameter duplicates in the stream, they will be encountered sequentially. This is important because the spool can only cache one result set at a time. As soon as a different set of correlated parameters in encountered, the spool 'rebinds' - it truncates its worktable, executes its subtree, and caches the new result.
The second (leftmost) spool is also a performance spool, but an explicit sort is not required, since the query processor can retain the order produced by the previous performance spool. The repartition stream iterator immediately below that second spool is a merging exchange - it preserves order as indicated by its Order By property.
A lazy spool on the inner side of a nested loops join is always an optimization for potential duplicate correlated parameters. Where the expected number of duplicates is not very high, the optimizer may use the spool alone (without a sort) or otherwise apply lesser optimizations to the nested loops join itself. Ultimately, the reason for the spool being in the plan is that the derived statistics used to choose a plan indicate that a significant number of duplicates on the outer side of the nested loops join are expected.
For testing purposes, you may disable this optimization using trace flag 8690. Testing with and without this flag determines how successful the optimization is in practice. This flag is not documented or supported by Microsoft, so you would not want to use it in a production system - though you could use the spool-less plan generated on a test system with a production plan guide, if the spool turns out to be counter-productive. Alternatively, you could rewrite or otherwise tune the query and indexing to generate a plan that naturally does not use a spool.
By SQLkiwi ♦ 6.6k answered May 07, 2013 at 04:23 PM