# Background

I was reviewing the query plan on a Stored Procedure and on seeing a Table Spool, specifically a Lazy Spool, I was drawn in to look under the covers.

# Lab

## Query

```

select
s.studentID
, s.studentName
, pc.productCD
, pc.schoolID
, pc.productID
, pc.title productTitle

from  dbo.student s

left join [dbo].ProductCatalog pc

on s.schoolID  = pc.schoolID
and s.courseID = pc.productID

```

## Execution Plan

### Operators – Table Spool

Here is the Table Spool ( Lazy Spool)

## Results

How is our results page

1. We only returned a single record

## Statistics Profile

One of the more insightful choices for reviewing a Query Plan is “set statistics profile on”.

### Tabulate

 Row Number # of Rows # of Executions Node ID Parent Physical Op Logical Op Argument Defined Values 21 1 1 57 3 Table Spool Lazy Spool 3 1 1 3 2 Nested Loops Left Outer Join 2 1 1 2 1 Compute Scalar Compute Scalar SchoolID 22 1 1 58 57 Clustered Index Seek Clustered Index Seek OBJECT:([dbo].[ProductCatalog].[PK_ProductCatalog] AS [pc]), SEEK:([pc].[schoolID]=’Long Island’ AND [pc].[productID]=’C0000055′) ORDERED FORWARD [pc].[schoolID], [pc].[productID], [pc].[productCD], [pc].[title]

### Analysis

1. Row Number = 21, Table Spool
• Node ID= 57
• It is a Lazy Spool
• It’s parent row is Row Number 3
2. Row Number = 3, Nested Loops ( Left Outer Join )
• We have our Left Outer Join
• It’s parent row is Row Number 2
3. Row Number =2, Compute Scaler
• Our query is simple
• And, the compute Scaler is the “Select” clause
• In the “Defined Values” cell, the columns we want returned are listed
4. Row Number = 22
• Node ID=58
• It’s parent row is 57
• It points us to the actual operator that feeds the Table Spool
• In this case a Clustered Index Seek ( to the ProductCatalog table )

### Takeaway

1. We are seeing the table spool because
• The table we are are joining against, dbo.ProductCatalog (pc), is being joined against using a Left Outer Join and not an inner join
• It is not compulsory to our query
• The columns that we reference in (pc) are not referenced in additional computations other than in the select columns
• And, so the SQL Engine goes about and does everything else and saves the result in a spool
• And, then joins the contents of the Spool to our referenced table
2. Join against Referenced table (pc)
• It is a Nested Loop join
• And, so it is fast
• Also it is index based, a Clustered Index Seek

## Statistics IO

A quick check of the results of Statistics IO is also affirming.

We have a worktable reference

1. Scan count = 1