Its my first time, any suggestions for diagnosing this plan?

SQLTweaker 2016-09-21 01:16:54

This is from a stored procedure that I have to run to delete certain records. It takes hours to run to delete over 100,000 records. It is a complicated delete but there has to be somewhere in there that could be more efficient.

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.

Vlady Oselsky 2016-09-21 20:04:35
Unless I'm missing something, the plan you posted is for a delete on a single table. The only concern here is that you have to do KeyLookup which means you are searching for the record to delete by another column, not the primary key. Lastly, delete effect total of 6 indexes, clustered + 5 non-clustered, which is not an extreme number by any means.
SQLkiwi 2016-09-22 07:16:22
There is nothing too dramatic in the plan, so most likely the poor performance is due to blocking, as you say.

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.