I got the question the other day, when are you likely to see a spool in an execution plan? Easy, whenever SQL Server needs to walk through the data multiple times, usually in JOIN operations… Yeah, well, once again, my flip answers are not quite the entire story.
Spool operations are temporary storage of the data for later reuse in a query plan. There are two types of spool operations, eager spool and lazy spool. A spool is basically a temporary table created within the execution of the query that is used when it’s likely that data will be needed again, and again during the execution of the query. This is not an explicit #temp temporary table, but a work table for operations within the processing necessary for a given query’s behavior. A spool is created when the optimizer thinks that it can work better with a semi-permanent sub-set of data rather than have to perform multiple seeks or scans against a table or index or in other places where data re-use is important (more in a bit).
So how does this work? Take a look at a simple query:
UPDATE Person.Person SET FirstName = 'Ted' WHERE FirstName = 'Ted';
When the execution plan for this query is generated, it looks like this:
In this case, an eager spool is used as part of the roll back mechanism and to prevent the Halloween scenario. An eager spool is one where the data is retrieved immediately.
It’s possible to see the other type of spool in a query that looks like this (straight out of the Books Online):
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS (    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel    FROM HumanResources.Employee    WHERE ManagerID IS NULL    UNION ALL    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1    FROM HumanResources.Employee e        INNER JOIN DirectReports d        ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, EmployeeLevel FROM DirectReports ;
Which would result in this execution plan:
Now you see a table spool that is called a lazy spool. This means that it only loads data as the data is requested. This makes a lot of sense because the lazy spool is operating as the means for gathering the recursive data together. So it’s not going to go and get all the data available, like an eager spool. Instead it’s going to only load the data as needed, lazy.
These two scenarios are much more likely than the typical join to show a table spool. Yes, it can, and does, appear in join operations, but as I said at the beginning, that’s such a flip answer. Much better to try to be complete.
nice article once again. Would you say that seeing spools in a query plan is good or bad, or depends. I’m trying to learn to spot areas of query-weakness through execution plans.
Thanks.
No, I wouldn’t say a table spool is one of those things that should automatically set off alarm bells. You know what’s causing them in most cases. But you will see spools sometimes (I can’t remember specifics at the moment) when it is a “bad thing” in the execution plan. You just need to be able to identify why it’s there in order to identify whether or not it’s a problem.
[…] optimizer in How the Query Optimizer Uses Statistics. Another article worth reading is Spools in Execution Plans. Understanding how the optimizer works in your databases server is the key to write good SQL […]
[…] optimizer in How the Query Optimizer Uses Statistics. Another article worth reading is Spools in Execution Plans. Understanding how the optimizer works in your databases server is the key to write good SQL […]
Table Spools crop up all the time in window functions.
[…] more on spools, you can read an older post of mine here, excellent information from Fabiano Amorim here, and an example of forcing a spool to improve […]
Judge Ray Harding
Spools in Execution Plans – Home Of The Scary DBA
[…] in his web post, Â Spools in Execution Plan (Â https://www.scarydba.com/2009/09/09/spools-in-execution-plans/), did a good job describing this Table Spool […]