1

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.

Improve this question
1
  • 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)
    – Martin Smith
    Jul 18, 2016 at 13:43

1 Answer 1

Reset to default
6

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.

Improve this answer
4
  • The thickness of the arrows doesn't look as though a large quantity of rows is being updated.
    – Martin Smith
    Jul 18, 2016 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.
    – Puneet Pathak
    Jul 18, 2016 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.
    – Puneet Pathak
    Jul 18, 2016 at 14:06
  • @PuneetPathak - Yes I think if you update the partitioning key that might force a wide plan.
    – Martin Smith
    Jul 18, 2016 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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