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)
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
