The SQL Server execution plan shows the Lazy spool has 7935322 actual number of rows, but its child operator only has 32 rows.

Nested Loop (Inner Join, 2 rows)   <--- Hash match(3967661 rows)
       +--- Lazy Spool (7935322 rows)  <-- Merge Join (32 rows)

If lazy spool sourced from its child rows as a temp table, it should only have 32 rows at maximum. I don't understand why does it have 7935322 rows.

Lazy Spool properties

Actual Rebinds: 16 
Actual Rewinds: 3967645
Number of Executions: 3967661

Nested Loop properties:

Actual Number of Rows: 2
Number of Executions: 16

closed as off-topic by dezso, RDFozz, LowlyDBA, mustaccio, hot2use Oct 24 '17 at 6:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RDFozz, LowlyDBA, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • One of child operator produce 32 rows.There must be one more child operator.And join of these 2 child operator result in Lazy spool with rewind and rebind.you hvn't mention about other operator.So may be join between these 2 child operator is wrong including filter condition. – KumarHarsh May 5 '17 at 8:18

Spool operator ( “Lazy” Spool in this case) will create a temporary table in the TempDB database, and store all data (32 rows in this case) returned from the Merge join.

Nested loop join executed 3967661 times (outer input) and did a join with table spool (holding 32 rows). Out of 3967661 times 16 times the parameter of the join changed and had to reevaluate the inner result set (rebind) and 3967645 times inner result set was reusable. As I am not seeing the whole plan I believe 7935322 number came form the 16 reevaluating the inner data set.

In most cases creating an index will most likely get rid of the spool and make the query run faster. Because you do not have to create the temporary table in tempdb.

  • The Nested Loops shows only 2 actual number of rows. That means there are only 2 rows after inner join with 3967661 rows (outer loop) and 32 rows (inner loop). What are 7935322 rows indeed? – Buzz May 5 '17 at 1:02
  • 1
    can you paste the whole plan here. – SqlWorldWide May 5 '17 at 1:04
  • Very sorry for that, but firm policy does not allow to. – Buzz May 5 '17 at 1:07
  • 1
    @buzz try using Plan Explorer to anonymize your plan, if company policy won't permit posting actual plans. sentryone.com/plan-explorer – AMtwo May 7 '17 at 3:28
  • 1
    Note that 3967661 * 2 = 79335322. One suspects that's relevant in some way. – RDFozz Oct 23 '17 at 16:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.