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)
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.
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
Hi,
Thanks for sharing this. But how does it matter in Performance? how can we avoid it?
Regards,
Yogesh
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
Hi Kanchan,
In SQL Server 2014, above query uses clustered Index Scan not spool.
Thanks for the feedback Birendra, I’ve used SQL Server 2012 while writing this post.
Regards,
Kanchan
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.
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?