Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data (say, 400k rows) which seemed fine, but I don't understand one thing.

Stripped down view of data model:

table ParentObject 
      int parentObjectId (PK)

table Child
      int childId (PK)
      int parentId (FK)

table GrandChild
      int grandChildId (PK)
      int childId (FK)
      <more stuff>

Where a parent object might have 200,000 Children, and a Child has 2 or so GrandChildren. I am interested in tuning the performance of:

DELETE FROM ParentObject WHERE parentObjectId = %d;

On Grandchild, there is an extra nonclustered index on (childId, + two other columns) as well as the primary key index. On child there is an extra nonclustered unique index (parentId, + two other columns).

The thing that I saw in the query plan is that while deleting the Grandchild objects, there were two expensive sorting operations mixed in with the deletions, and I don't understand why they are there.

What should I be looking at to help this delete operation go faster? Does it need to sort? Would it help if I denormalized the ids and added a parent Id to the grandchild table? Did I build my index stupidly?

The full execution plan is here.

share|improve this question
Can you add the execution plan to your question please. The sorts are probably there to optimise the delete operation on the non-clustered indexes (wide vs narrow plan). – Mark Storey-Smith Dec 14 '12 at 16:22
And please post an actual execution plan file somewhere (not an estimated plan, and not a screen shot of the graphical plan - the actual .sqlplan file is much more useful). – Aaron Bertrand Dec 14 '12 at 16:24
fixed permissions on google doc, sorry. – Mikeb Dec 14 '12 at 18:30
up vote 11 down vote accepted

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:

DML Request Sort

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:


eager index spools

Examples of tables that are currently being scanned below a nested loops join are:


scan below nested loops

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 child7s.Id.

From that information, it seems a good access method (index) on child7s for this part of the query would be keyed on parentObjectId with 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:


hash joins

The table 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.

sort before merge

Once the basic indexing issues are resolved, we can look at other optimizations if necessary.

share|improve this answer
Thanks for the very detailed answer. For the cases where there is an index scan and a table spool below an inner join, these are mostly cases where the PK Index of a child object (the index) is being merged with the parentId (the table spool) - would it be appropriate to add the parentId to the existing index as an included column? Or better to create a new index around the parentId? – Mikeb Dec 14 '12 at 20:03
@Mikeb The ideal access path would be a seek on parentId with Id as an included column. I have edited the answer to include details there (oh, and adding parentId to the existing index as an included column would not allow for an index seek). – Paul White Dec 14 '12 at 20:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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