SQL Server Table Spool Operator (Lazy Spool) – Part3

Hi Geeks,

Welcome to the Part3 of SQL Server Table Spool Operator (Lazy Spool). If you reading this post before earlier two posts on the subject, you can click on Part1 and Part2 to read them ahead of today’s post.

Today, we will be exploring second part of the execution plan (highlighted in RED) to sum up the Table Spool operator.

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)

TableSpoolGrphPlanSS

Highlighted section (in RED) of the plan will run for the sub-query that returns average of Order quantity represented by OrderQty column in SalesOrderDetail table. Let us go with each of these operators one by one.

To start with, execution plan reads the data from the Table Spool (Lazy Spool) and passes the results to the Stream Aggregate to calculate average for each groups as decided by Segment Operator (discussed yesterday in Part2).

The Stream Aggregate operator will calculate the average of OrderQty column and return one row as output.

StreamAggrTextPlan

Compute Scalar operator will convert the result of aggregation to a numeric value (see image below) and pass the output to NestedLoops.

ConvertImplicit

Last Table Spool operator (just below the Nested Loops shown in second part) used to read grouped rows from Spool table which will be joined with the result of Compute Scalar.

The Nested Loops operator in the second part in the execution plan performs an iterative inner join. In our example for each row returned by the computed scalar it scans the Spool table and returns all rows that satisfy the condition of the join. To be specific, it returns the rows where the OrderQty column in the Spool table is higher than the OrderQty calculated in the aggregation.

NestedLoopsTextPlan

To conclude, Nested Loops (in first part of the execution plan, highlighted in BLUE), scans the table and joins it with another, one row at a time. In our example, for each row in the Table Spool item (in the first part of the execution plan) the nested loop will join the result of the OrderQty column in the spool table where the value is higher than the OrderQty as calculated in the Stream Aggregate. When this step is finished, the Spool operator will be called again in the first part of the execution plan, which in turn will call Segment operator that leads to another segment of rows. This process continues until all rows are read in the table.

See you tomorrow, till then.

Regards,

Kanchan

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

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 →

Leave a Reply

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