Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Sometimes we see operators like Eager Spool, Lazy Spool In the Execution Plan .
In this article we will see what these operators do. Eager Spool and Lazy Spool are logical operators.
In general, we see these expressions with physical operators such as Index Spool, Table Spool, Window Spool, and Row Count Spool.
What is the purpose of these operators and when we see them in the execution plan?
1)If the selected dataset is needed again later;
If the sql server query needs the same data (such as a result of a calculation) again during operation, it will create a work table under tempdb and insert it into it. And when this query is needed during the operation, data is retrieved from this work table.
2)When the data must be kept away from the original source;
Many people have not heard the Halloween scenario. This problem was discovered in 1976. The physical location of the row changes after an update in the database.
On the Halloween day, a company has run a query to raise 10% to whose salary is less than $ 25,000, and the query worked properly, but at the end of the query the salary of everyone in the database was $ 25,000.
Because the query continued to raise until the limit of $ 25,000 was reached. That’s why it’s called the Halloween problem.
In SQL Server, with Hallowen Protection, it has a mechanism for preventing this problem. That keeps the data separate from its original location (with spool operators)
Logical Operators
| Eager Spool | We have said that Eager Spool and Lazy Spool are always seen with other physical spool operators. When a request from the previous operator is received, Eager Spool takes all the rows at once and transfers them to TEMPDB. (You can see it in the Hallowen scenario.) |
| Lazy Spool | When a request from the previous operator is received, Lazy Spool only takes that row and transfer it to tempdb. You can see it with the Nested Loop operator. Because the line-by-line reading is performed in the nested loop |
Sometimes we see too many spools in long tsql code or stored procedures.
Once, a query by developed a friend filled out the tempdb because of these spools.
To understand how I found the source of problem, you should first read the article entitled “Which Queries Fill the Tempdb“.
I have seen in the above article that objects created internally have filled TEMPDB.
With the script that shows the queries that fill Tempdb, I also see which query consumes tempdb and tell the related person to correct the problem in the script.
In my case this person was trying to produce a report of the last 3 years by fetching data from more than 10 tables from 3 different databases.
The problem was resolved when we run the same query with 3-month periods.
