{"id":888,"date":"2010-05-20T00:00:00","date_gmt":"2010-05-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/operator-of-the-week-spools-eager-spool\/"},"modified":"2021-08-16T15:02:16","modified_gmt":"2021-08-16T15:02:16","slug":"operator-of-the-week-spools-eager-spool","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/operator-of-the-week-spools-eager-spool\/","title":{"rendered":"Operator of the Week – Spools, Eager Spool"},"content":{"rendered":"
\n

Spool Operators<\/h2>\n

\"1034-Spool1.JPG\"
\nEager Spool<\/p>\n

\"1034-Spool2.JPG\"
\nLazy Spool<\/p>\n

\"1034-Spool3.JPG\"
\nRow Count Spool<\/p>\n

\"1034-Spool4.JPG\"
\nTable Spool<\/p>\n

\"1034-Spool5.JPG\"
\nNon-Clustered Index Spool<\/p>\n

This week we’ll be featuring the Spool showplan operator. There are five types of Spool operations, each with its own behavior, and idiosyncrasies, but they all share in common the way that they save their intermediate query results on the TempDB<\/b> database, and use this temporary area to search a value.<\/p>\n

There are many tricks that Query Optimizer uses to avoid any logical problems and to perform queries better: The spool operators are a good example of this. This month, we’ll see all these types of Spool operations, one by each week.<\/p>\n

A spool reads the data and saves it on TempDB<\/b>. This process is used whenever the Optimiser knows that the density<\/a> of the column is high and the intermediate result is very complex to calculate. If this is the case, SQL makes the computation once, and stores the result in the temporary space so it can search it later.<\/p>\n

The spool operators are always used together with another operator. As it stores values, it needs to know what these values are, and so it must receive them from another operator. For instance, one spool can be used together with a clustered index scan<\/b> in order to save the rows read by the scan. These rows can then be read back to an update<\/b>, or a select <\/b>operator.<\/p>\n

A quite simple graphic representation about what I said above could be the following picture:<\/p>\n

\"1034-Spool6.JPG\"<\/p>\n

In the example represented by these icons above, the order of execution is: Clustered Index Scan<\/b> sends rows to Spool<\/b> and the Select <\/b>reads all of these rows directly from the Spool.<\/p>\n

Eager Spool<\/h2>\n

We’ll start with the Eager Spool Operator. The role of the Eager Spool is to catch all<\/b> the rows received from another operator and store these rows in TempDB<\/b>. The word “eager” \u00a0means that the operator will read ALL rows from the previously operator at one time. In other words, it will take the entire input, storing each row received.<\/p>\n

In our simple sample (SCAN -> EAGER SPOOL -> SELECT) the eager will works like this: When the scanned rows are passed to Spool then it gets all the rows, \u00a0not one row at the time you’ll notice but the entire scan, and keeps them in a hidden temporary table.<\/p>\n

Knowing that, we could say that The Eager Spool<\/b> is a blocking operator. I believe that I haven’t yet written about the difference between blocking operators and non-blocking operators. Let’s open a big parenthesis here.<\/p>\n

There are two categories of showplan operators: The “non-blocking” operators and the “blocking” operators or “stop-and-go”:<\/p>\n