SQL Server Eager Spool operator – Part2

Hi Geeks,

I hope you liked Part1  of SQL Server Eager Spool operator. To start from where we left yesterday, today we are going to explore what happens if SQL Server choose to select non-clustered index to read the rows to be updated.

Let me modify update statement to include non-clustered index IX_Price as following.

USE [tempdb]

UPDATE Inventory SET Price = Price * 1.1
FROM Inventory WITH (INDEX = IX_Price)
WHERE Price < 100.00





From above execution plans, we see that after reading the data from non-clustered in IX_Price SQL Server uses Table Spool(Eager Spool) blocking operator. It reads all data and then moves to next operator. In our example, Eager Spool will read all data from IX_Price then move to tempdb and hence later on UPDATE doesn’t read non-clustered index IX_Price anymore and instead all reads are performed using Eager Spool operator.

If SQL Server hadn’t use eager spool operator it would have read the rows directly from non-clustered index IX_Price as read first row, update then proceed with the next row and so on. The problem is in such scenario, row position would have been repositioned in non-clustered index.

In short index needs to keep the data sorted in Price column which may have resulted same column to be updated many times which is known as Halloween Problem and detailed discussion of which is beyond scope of this blog post but yes, Eager Spool can be used to avoid this in SQL Server.

That’s all for today. In case you missed my earlier posts on One operator a Day series, you can click here  to read them.

Happy Learning!



Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook | Follow me on Twitter | Follow me on FaceBook


About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

One Comment on “SQL Server Eager Spool operator – Part2”

  1. Pingback: Homepage

Leave a Reply

Your email address will not be published.