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)
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.
Compute Scalar operator will convert the result of aggregation to a numeric value (see image below) and pass the output to NestedLoops.
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.
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
