The actual number of row of Lazy Spool is huge 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)   &lt;--- Hash match(3967661 rows)
       ^
       |
       +--- Lazy Spool (7935322 rows)  &lt;-- 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
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.