Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 255

A logical operator is a conceptual mechanism for describing a query processing operation. An example
of this is an INNER JOIN.

A physical operator actually implements the logical operator using some actual method or technique. A
Nested Loop Join is a physical operator, which implements the logical operator INNER JOIN.

Spool operators can be either logical or physical. The following is a list of physical spool operators:

❑ Table Spool: The table spool operator scans the input (from the child operator) and places a

copy of each row in a table in tempdb. The entire input from the child operator is written into the
tempdb table before proceeding. If the parent operator is rewound, such as a Nested Loop
operator, and no rebinding is needed, the spool table can be used with no further processing.

❑ Non-clustered Index Spool: The Index Spool operator scans its input rows (from the child oper-

ator) and places a copy of each row in a table in tempdb. The entire input from the child operator
is written into the tempdb table before proceeding. It then builds a non-clustered index on the
rows. This allows the system to use the seeking capability of indexes. If the parent
operator is rewound, such as a Nested Loop operator, and no rebinding is needed, the spool
table can be used with no further processing.

❑ Row Count Spool: The Row Count Spool operator scans its input rows (from the child operator),

and counts the rows. The rows are also returned without any data in them. This is useful when
checking for the existence of rows, rather than the data itself. For example, a query may include
an EXISTS or NOT EXISTS predicate. Thus, the content of the data isn't important, only the fact
that a row does or does not exist.

The following is a list of logical spool operators:

❑ Eager Spool: The Eager Spool operator scans its input rows (from the child operator), and places

a copy of each row in a table in tempdb. The entire input from the child operator is written
into the tempdb table before proceeding. If the parent operator is rewound, such as a Nested
Loop operator, and no rebinding is needed, the spool table can be used with no further process-
ing. If rebinding is needed, the spooled table is thrown away and the spool table is rebuilt by
rescanning the input (from the child operator).

❑ When the eager spool's parent operator asks for the first row, the spool operator builds its spool

table by writing all the rows from the input (from the child operator).

❑

Lazy Spool: The Lazy Spool operator builds its spool table in an ''as requested'' manner. That is,
each time the spool's parent operator asks for a row, the spool operator gets a row from its input
operator and stores it in the spool table. This is different from the eager spool, which must gather
all the rows from the input operator before proceeding.

Sometimes, spooling intermediate results to tempdb is cheaper than retrieving all of the rows from the
child operators. Spools can provide a lot of benefit, especially when processing a complex query is
necessary. However, they can also generate performance robbing I/O while they manage the tempdb
table. For example, an eager spool, which has to be repeatedly reprocessed because of rebinding, can be
particularly nasty.

When you have a spool operator in a query plan that you think is causing a bottleneck, you have a
couple of options. First, try rewriting the query such that spools aren't necessary. Another option is to
try and perform the spool operation yourself. Sometimes creating and populating your own tempdb will