Your question regarding 'why' the optimizer chooses one type of plan over another isn't easily answered, unfortunately. The heuristics and complexities invovled in the query processor are so intricate that it's very hard to tell why it chose the plan it chose. The short answer is that it found its plan to be cheaper than the others it considered.
Spool operators are typically introduced into a query plan to 'cache' the results of complex query elements (large subqueries or remote scans, for instance), or to provide "Halloween protection" for update queries. The operator takes the rows produced by its children (those operators underneath it in the SET STATISTICS PROFILE tree) and stores them off into a table in TEMPDB so that it does not have to rescan or reevaluate the operators underneath it. In the case of a normal spool (usually referred to as an 'eager spool' (ES)), when the parent of the spool asks for a row, then the ES asks for ALL of the rows from the operators underneath it. In the case of a lazy spool (LS), the spool only stores the individual rows that are requested by its parent, not all of them. Many times, this is cheaper than retrieving all of the rows from its child. Unfortunately, while spools can provide a lot of performance benefit in preventing complex query elements from being updated over and over again, they can generate quite a lot of IO while they create the TEMPDB worktable.
Is the IO cost the reason that you are askign your question? Is this a normal SELECT or an UPDATE query? Perhaps some context would help. Can you post the query plan from SET STATISTICS PROFILE ON? Perhaps running your query through the index tuning wizard, or otherwise trying to optimize the index structure underlying your query would eliminate the need for the operator...
Thanks,
Ryan Stonecipher
SQL Server Storage Engine
"david_0" <do*****@yahoo.com> wrote in message news:58**************************@posting.google.c om...
What causes the query optimizer to choose a table spool\lazy spool
action in the execution plan? The explanation of "optimize rewinds"
makes little sense because my query never comes back to that table.
I'm going to have to change the query but it would be helpful if I
knew what I should be trying to avoid.
David
