Row Count Spool

Introduction

The Row Count Spool operator is one of the four spool operators that SQL Server supports. It counts the number of rows in its input data, and can then later return that same amount of rows, without having to call its child operators to produce the input again.

The Row Count Spool can be viewed as similar to Table Spool, but optimized for cases where the amount of rows is relevant but their content is not. Because the content of the rows is not relevant, the operator does not need to use tempdb to store its input in a worktable; it only has to keep a running count as it reads the input. The other two spool operators have different use cases: Index Spool is used to enable the spool operator to return specific subsets of the input multiple times, and the Window Spool operator is used to support the ROWS and RANGE specifications of windowing functions.

Because Row Count Spool is used in cases where the same number of rows is needed, but not their content, it returns empty rows. These rows are 9 bytes long, for the standard row header; there are no actual data columns in them. And since the operator ignores whatever data columns are in its input, its child operator typically returns empty rows as well.

Unlike the other spool operators, Row Count Spool does not need to store any rows. The rows it reads are discarded; the empty rows it returns are constructed on the spot. The only things a Row Count Spool store are a counter of the number of rows it found in its input, and a counter of the number of rows already returned on the current execution.

You may for example find a Row Count Spool when you perform a cross join for the sole purpose of generating extra copies of each row, or when a query includes an EXISTS predicate with a non-correlated subquery.

Execution modes

A Row Count Spool operator can function in two different execution modes, as determined by the Logical Operation and Primary Node ID. The options are as follows:

• Eager builder: Logical Operation is Eager Spool and Primary Node ID is absent.
  On the first execution, an eager builder first reads the entire input when the first row is requested to count the number of rows, then returns a single empty row; it then returns additional empty rows up to the number of rows in the input as more rows are requested. Subsequent executions return empty rows on each request up to the number of rows previously counted on the input, unless a rebind is issued.
• Lazy builder: Logical Operation is Lazy Spool and Primary Node ID is absent.
  On the first execution, a lazy spool reads a row, counts it, and immediately returns an empty row for each read request. Subsequent executions return empty rows on each request up to the number of rows previously counted on the input, unless a rebind is issued.
• Consumer: Primary Node ID is present.
  A consumer returns empty rows equal to the number of rows counted by another Row Count Spool operator, as indicated by the Primary Node ID. In this case only, the Row Count Spool operator does not have a child operator. The Logical Operation can be marked as either Eager Spool or Lazy Spool without affecting the execution mode (as far as known the Logical Operation of the consumer Row Count Spool is always the same as that of the Row Count Spool referenced by the Primary Node ID).

Visual appearance in execution plans

Depending on the tool being used, a Row Count Spool operator is displayed in a graphical execution plan as shown below:

SSMS and ADS (current versions)	Legacy SSMS	Plan Explorer	Paste The Plan

Algorithm

The basic algorithm for the Row Count Spool operator is as shown below:

Note that this flowchart is a simplification. It doesn’t show that execution stops whenever a row is returned, and resumes where it was upon next being called. It also does not show that the operator’s input is closed when the operator itself is closed.

Consumer or builder

A Row Count Spool that operates as a consumer behaves the same as a builder Row Count Spool when it executes a rewind. The only difference is that the rewind returns a number of rows equal to the input counter computed on a previous rebind execution, whereas the consumer Row Count Spool returns a number of rows equal to the input counter of another Row Count Spool operator, as indicated in the Primary Node ID property.

Rebind or rewind

A Row Count Spool distinguishes between being initialized for a rebind or a rewind (see Nested Loops). Whether rebind or rewind is needed is known when Initialize() is called and then stored in operator memory to be used on the first GetNext() call.

The first execution of the operator cannot be a rewind, so it should always be considered a rebind (even though SQL Server itself does not consistently consider it as such; it is sometimes considered as a rebind, but sometimes also considered as neither of the two).

Input counter

The input counter is simply a memory location in the operator’s working memory that stores a tally of the number of rows read in the input. When the operator runs as an eager spool, the entire input is consumed and counted at once, after which this counter is the number of rows in the input. When the operator runs as a lazy spool, then each rebind counts rows as they are read and corresponding empty rows are immediately returned; in this case the counter is a running count. On a rewind, the input counter never changes and indicates the total number of empty rows that need to be returned.

Iteration counter

The iteration counter is another memory location in the operator’s working memory, storing another tally. In this case of the number of empty rows returned. For a rebind of a lazy spool, the iteration counter is not needed. For rewinds, as well as for all executions of eager spools, the iteration counter keeps track of the number of empty rows returned since the execution started; once this is equal to the input counter, the operator will return an end of data signal.

Operator properties

The properties below are specific to the Row Count Spool operator, or have a specific meaning when appearing on it. For all other properties, see Common properties. Properties that are included on the Common properties page but are also included below for their specific meaning for the Row Count Spool operator are marked with a *.

Property name	Description
Estimated Row Size *	Always 9 bytes (the size of the row header), because a Row Count Spool always returns empty rows.
Logical Operation *	Can be Eager Spool or Lazy Spool, as described above.
Output List *	Since a Row Count Spool always returns empty columns, it does not have an actual Output List. The property is still present, though, but empty.
Primary Node ID	For consumer mode only, this property references the Node ID property of another Row Count Spool operator. The Row Count Spool will return as many empty rows as that referenced spool operator counted in its input.

Implicit properties

This table below lists the behaviour of the implicit properties for the Table Spool operator.

Property name	Description
Batch Mode enabled	The Row Count Spool operator supports row mode execution only.
Blocking	The Row Count Spool operator is fully blocking when it executes as an eager producer; in other execution modes it is non-blocking.
Memory requirement	The Row Count Spool operator does not have any special memory requirement.
Order-preserving	All rows returned by a Row Count Spool operator are empty, so they are all equal. The result can be considered both ordered and unordered, and neither has any relevance for other operators.
Parallelism aware	The Row Count Spool operator is not parallelism aware.
Segment aware	The Row Count Spool operator is not segment aware.