CODEWITHSUNDEEP
sql-server
Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

Table Spool in SQL Server execution plan

Is it really bad to get 'Table spool' in sql server execution plan? If not how it is advantageous? Do we really look for getting rid of Table Spool?

Upvotes: 1

Views: 906

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

According to MSDN:

The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

It's always better to have no operator than to have one. Advantages are described above (no rescanning). Disadvantage is that rows must be stored in tempdb (usually fits in memory for faster access).

Usually it's not bad to have this operator unless everything fits in memory. You must share execution plan/query for more datailed explanation and possible tweaks.

Upvotes: 2

Related Questions