Its my first time, any suggestions for diagnosing this plan?
If nothing else I am learning about eager spool and how to use this tool.
I had to anonymize since this is from my employers database.
I'm not new to reading the plans but digging further is where I am still a noob.
Thanks for any helpful input!
I should add I was seeing the application and this sproc blocking each other and there are over 80 tables it has deleted from so far in just over 3 hours. That would explain part of the reason this thing takes hours to run.
The Sort Distinct does indicate that the delete is not written in a way that uniquely identifies a target row. You could likely avoid this by creating a suitable unique index on the heap table driving the delete. This is a good practice in any case.
The Key Lookup could be eliminated by adding the column(s) used in the residual predicate on that operator to the index, either as a key column or include.
Maintaining the indexed view may require local escalation to serializable isolation, depending on the definition of the view, which might exacerbate the locking. There's probably not too much you can do around that, aside from the normal things around reducing contention, but it is something to bear in mind.