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?