To answer your main question directly, the sorts are there to present rows to update operators (performing deletions in this case) in index key order. The principle at work here is that sorting on the keys will promote sequential access to the index.
This can be a good optimization, though the details depend on your hardware, how likely the affected pages are to be in memory, and whether the sorts can complete within the memory allocated to them. When the optimizer decides the cost of sorting will be paid back by the increased efficiencies associated with sequential index access, it sets a property
DMLRequestSort on the update operator:
The optimizer may also decide to split the update into separate operators to maintain the clustered index (or heap) and then the nonclustered indexes. often, it will decide to sort more than once - first for the clustered index keys, and then again for the nonclustered index(es). Again, where sorting is considered optimal, each index update operator will have the
DMLRequestSort property set to true.
All that said, the things I would fix first would be to eliminate the index scans where the join operator they feed is a nested loops join, and to remove the eager index spools, which are building an on-the-fly nonclustered index on their row source every time the query is executed. An eager index spool is often the clearest possible sign that you are missing a useful nonclustered index. The seek predicate in the index spool operator identifies the keys the optimizer would like an index on.
Examples of tables that are missing a nonclustered index (requiring an eager index spool) are:
Examples of tables that are currently being scanned below a nested loops join are:
Taking the example shown above, the Clustered Index Scan has an output list of
Id, parentObjectId, the Nested Loops Join predicate is
child7s.parentObjectId = parentObject.Id, and the join output column list is
From that information, it seems a good access method (index) on
child7s for this part of the query would be keyed on
Id as an included column. You should be able to work out how best to work this into your existing indexing strategy.
The following are examples of tables where the optimizer is currently choosing a hash join. I would check tables like this to ensure that is a reasonable access method:
child2bigChild also participates in a merge join where an explicit sort is necessary. Again, I would check to see if this sort could be avoided.
Once the basic indexing issues are resolved, we can look at other optimizations if necessary.