SQL Server Table Spool Operator (Lazy Spool) – Part1

Hi Friends,

To understand SQL Server Table Spool Operator, let us consider following query as an example.

USE [AdventureWorks2012]

SELECT SD1.SalesOrderID,SD1.SalesOrderDetailID
FROM Sales.SalesOrderDetail SD1
WHERE SD1.OrderQty > (SELECT AVG(SD2.OrderQty) FROM Sales.SalesOrderDetail SD2 
					  WHERE SD2.SalesOrderID = SD1.SalesOrderID)

TableSpoolGrphPlan

TableSpoolTextPlan

For each SalesOrderID from the SalesOrderDetails table the sub-query returns the average value of all OrderQuantity and after that average is compared with principal query to filter each SalesOrderID’s with values higher than their average.

Lazy Spool reads data only when individual rows are required and creates a temporary table then build this table in a “lazy” manner; that is, it reads and stores the rows in a temporary table only when the parent operator actually asks for a row.

As can be seen, Spool operator displayed three times in execution plan but that doesn’t mean that three temporary tables are being created in memory that to say, all the spools are using same temporary table and this can be verified if we look at the operators hints displayed in execution plan.

ToolTip1ToolTip2ToolTip3

As we see, the first spool hint has NodeID 2 and other two has referenced Primary Node ID 2. Next two days we are going to discuss on the execution plan for the query used in our example.

Happy Learning!

Regards,

Kanchan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

6 Comments on “SQL Server Table Spool Operator (Lazy Spool) – Part1”

    1. Hi Yogesh,

      There are different logical spool operators like Lazy Spool, Eager Spool (we are going discuss in near future). The difference between them are how data is populated for each of these operators.In general Spool temporary tables are referenced as worktables in I/O stats.

      Though spools can improve performance at the same time it could be an overhead to have unnecessary spools which we may want to remove by creating appropriate indexes.

      Thanks,
      Kanchan

  1. Nice Article.

    “Next two days we are going to discuss on the execution plan for the query used in our example.”
    Can you please post link for above please.

  2. Hi,

    Thank you for sharing the info on spool. I know that the optimizer chooses the Eager spool to avoid halloween effect but when the optimizer chooses the Table lazy spool and Why? Can you please explain?

Leave a Reply to Kanchan Bhattacharyya Cancel reply

Your email address will not be published. Required fields are marked *