SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Spools in Execution Plans

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

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Anonymous on 10 September 2009

Pingback from  Dew Drop – September 10, 2009 | Alvin Ashcraft's Morning Dew

Posted by Tim Mitchell on 11 September 2009

Good explanation - I have to admit that I've not spent any time investigating spools in execution plans.  This is a helpful tip.

Posted by awoodhouse on 8 October 2009

After just spending a significant portion of my time trawling the internet, i came here and found the exact answer in seconds. Now i know why my update statement is being spooled. The halloween scenario has definitely opened my eyes a bit to a potentially volatile situation.


Leave a Comment

Please register or log in to leave a comment.