When I update or delete on partition table it shows eager spool in execution plan.

Image for Update query execution plan showing eager spool:- Image for Update query execution plan showing eager spool

I can’t understand why it is doing eager spool only when I am updating or deleting on partition table, same query when I run on non partition table it does not do eager spool.

  • Can you add a script repro-ing this so we can see what your exact situation is in case any of it makes any difference and forces a wide update plan (e.g. in terms of whether the indexes are partition aligned any unique constraints etc) Jul 18 '16 at 13:43

The eager spool has to do with the size of data you're deleting.

In this case, you're updating a large quantity of rows on a table with several indexes. You're also updating a field that's covered by a lot of nonclustered indexes, and SQL Server has to update all of those indexes as well.

To avoid this operation, update less rows (as a percentage of the table) or index the table less heavily.

  • The thickness of the arrows doesn't look as though a large quantity of rows is being updated. Jul 18 '16 at 13:41
  • Martin you are right i am not updating large quantity of rows. I am updating single row at a time, but the columns that i am updating includes a column "starttime" which is my partition key, also some columns on which indexes are their. Jul 18 '16 at 14:05
  • Hi Brent, Thanks for reply. I had a BOOKING table, I created a copy of this table BOOKING_PART and partitioned this table. Now when i update or delete BOOKING_PART it shows eager spool. Whereas when i update or delete BOOKING it does not shows eager spool. Both tables are exactly same with same indexes. Jul 18 '16 at 14:06
  • @PuneetPathak - Yes I think if you update the partitioning key that might force a wide plan. Jul 18 '16 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.