When I run this delete:

DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870

...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and primary key. But (according to the execution plan) it seems to be hitting 361,190 rows and using other indexes. The table does have a field with an XML data type (in case that affects this DELETE).

Any ideas why and how I can speed up this DELETE?

Execution Plan here: http://sharetext.org/qwDY Table schema here: http://sharetext.org/Vl9j



The top levels of the plan are concerned with removing rows from the base table (the clustered index), and maintaining four nonclustered indexes. Two of these indexes are maintained row-by-row at the same time the clustered index deletions are processed. These are the "+2 non-clustered indexes" highlighted in green below.

For the other two nonclustered indexes, the optimizer has decided it is best to save the keys of these indexes to a tempdb worktable (the Eager Spool), then play the spool twice, sorting by the index keys to promote a sequential access pattern.

Regular index maintenance

The final sequence of operations is concerned with maintaining the primary and secondary xml indexes, which were not included in your DDL script:

XML index maintenance

There is not much to be done about this. Nonclustered indexes and xml indexes must be kept synchronized with the data in the base table. The cost of maintaining such indexes is part of the trade-off you make when creating extra indexes on a table.

That said, the xml indexes are particularly problematic. It is very hard for the optimizer to accurately assess how many rows will qualify in this situation. In fact, it wildly over-estimates for the xml index, resulting in almost 12GB of memory being granted for this query (though only 28MB is used at runtime):

Estimated row counts

You could consider performing the deletion in smaller batches, hoping to reduce the impact of the excessive memory grant.

You could also test the performance of a plan without the sorts using OPTION (QUERYTRACEON 8795). This is an undocumented trace flag so you should only try it on a development or test system, never in production. If the resulting plan is much faster, you could capture the plan XML and use it to create a Plan Guide for the production query.


You're on the right track - the XML index is the problem. Obviously, there's a primary as well as a secondary XML index.

When performing a DELETE against the base table (ETLHeaders) the data have to be deleted from every index of this table, too. This overhead can be significant, especially for XML indexes.

The index causing the long duration is the secondary XML index [XML_IX_ETLHeaders_Property]. The 39,157 rows in your "relational table" refer to 361,190 rows in the primary XML index [XML_IX_ETLHeaders]. And those 361k rows need to be sorted in order to be able to be used to delete the secondary index. And this sort operation is causing the long duration of the query. (As a side note, the index statistics of both xml indexes seem to be way off: the actual data size of the 361k rows of primary xml index is 160MB whereas the estimated data size is almost 4TB (yes, 4 TerraByte!!)).

The only option I see to speed up this query is to eliminate the secondary XML index. Depending on the data it might be a better option to shred the XML data into a relational table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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