Hey Guys,

I have a good one!

What would cause the server to use a huge index but then perform a index
   spool/eager spool action in the query plan and basically recreate the
whole index?

Thanks,

Frank

The query optimizer may have chosen a plan that requires the rows to be in a
different order for two different operations.  For example, the optimizer
may choose to process the restrict condition (WHERE restrict_condition)
before processing the join condition (ON (join_condition)), where the index
used to process the restrict condition is in a different order than is
needed to process the join.  If the execution plan contains an iteration
step, then the eager spool may be used to minimize the number of reads
required to satisfy the join.  The SEEK:() predicate on the Index Spool
operator restricts the rows that are copied into tempdb, which minimizes the
number of reads required for lookups in each iteration.

Show quoteHide quote

What is SQL Server version of Oracle's CPU_PER_CALL, or how do I restrict max. exec. time of a query
SImple code error
How Do I Avoid Update Deadlock
ordered insert - select is not inserting in order
Delete Without Writing To Transaction Log