Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 134

continued

Anytime you see hash warnings, it is a potential indicator of performance problems
and should be investigated.

Possible solutions to hash warnings include the following:
➤

Increase memory on the server.

➤

Make sure statistics exist on the join columns.

➤

Make sure statistics are current.

➤

Force a different type of join.

Spool Operators
The various spool operators are used to create a temporary copy of rows from the input stream
and deliver them to the output stream. Spools typically sit between two other operators: The one
on the right is the child, and provides the input stream. The operator on the left is the parent, and
consumes the output stream.

The following list provides a brief description of each of the physical spool operators. These are the
operators that actually execute. You may also see references to logical operators , which represent
an earlier stage in the optimization process; these are subsequently converted to physical operators
before executing the plan. The logical spool operators are Eager Spool, and Lazy Spool.

➤

Index spool — This operator reads rows from the child table, places them in tempdb, and
creates a nonclustered index on them before continuing. This enables the parent to take
advantage of seeking against the nonclustered index on the data in tempdb when the under-
lying table has no applicable indexes.

➤

Row count spool — This operator reads rows from the child table and counts the rows.
The rows are also returned to the parent, but without any data. This enables the parent to
determine whether rows exist in order to satisfy an EXISTS or NOT EXISTS requirement.

➤

Table spool — This operator reads the rows from the child table and writes them into tempdb. All
rows from the child are read and placed in tempdb before the parent can start processing rows.

➤

Window spool — This operator expands each row into the set of rows that represent the
window associated with it. It's both a physical and logical operator.

Scan and Seek Operators
These operators enable SQL Server to retrieve rows from tables and indexes when a larger number
of rows is required. This behavior contrasts with the individual row access operators key lookup
and RID lookup , which are discussed in the next section.

➤

Scan operator — The scan operator scans all the rows in the table looking for matching
rows. When the number of matching rows is >20 percent of the table, scan can start to out-
perform seek due to the additional cost of traversing the index to reach each row for the seek.
There are scan operator variants for a clustered index scan, a nonclustered index scan, and a
table scan.