Transact SQL – Execution Plan – Table Spool / Lazy Spool on Left Outer Join

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)
LeftOuterJoin-Operator-TableSpool-LazySpool

 

Results

How is our results page

Results

  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”.

Image

StatisticProfileOn

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

StatisticsIO

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

We have a worktable reference

  1. Scan count = 1
  2. Logical Reads = 0
  3. Physical Reads = 0

And, so we see that we do a single Scan, but we did not actually pay the penalty of Physical Reads or shall we say Disk reads.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s