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!

Regards,

Kanchan

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