SQL Server Table Spool Operator (Lazy Spool) – Part2

Hi Geeks,

Today we will explore the first step of execution plan (highlighted in red) in detail and is a continuation of yesterday’s post on SQL Server Table Spool Operator (Lazy Spool) – Part1. To recap, we ran following query and for which execution plan is same as given below the query.

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)

 

TableSpoolGrphPlanFS

First step in the execution plan is to read all rows from SalesOrderID and SalesOrderDetailsID column and is performed using Clustered Index Scan.

Segment Operator, divides the data into different groups and here in our example Grouped by SalesOrderID and is sorted on the same column. Given the fact this column is already sorted as it is also clustered index of SalesOrderDetail table, the operator just needs to read the rows until all groups are formed properly considering different ID’s. As an example, let’s say first segment is SalesOrderID equals to 43659 so when Segment operator is reading any changes it finish its job and next operator receives segment of all data for SalesOrderID 43659. This process repeats until all data is read.

Next comes Table Spool, working as Lazy Spool. This operator will create a temporary table on tempdb database and store all data returned by Segment Operator according to different groups.

Nested Loop join will combine, first and second parts of the execution plan, in other words principle query with the sub-query. We will come back to this operator again tomorrow as this has a link with second part if the execution plan that we are going to discuss tomorrow.

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 →

Leave a Reply

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