Spools in Execution Plans

September 9, 2009 at 2:16 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , )


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:

EagerSpool

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:

LazySpool

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.

4 Comments

  1. Chris Stobbs said,

    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.

  2. scarydba said,

    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.

  3. Log Buffer #161: a Carnival of the Vanities for DBAs | Pythian Group Blog said,

    […] 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 […]

  4. Log Buffer #161: a Carnival of the Vanities for DBAs « PlanetMysql.ru – информация о СУБД MySQL said,

    […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: