The Table Spool operation costs are more difficult to interpret. Both show I/O cost of 0.931 and CPU cost of 0.0188, but the top Spool shows a total cost of 0.768492 while the lower Spool shows a total cost of 1.149179. Both Spool operations show a total subtree cost of 1.149179.
Upper Spool for Delete plan using Index Seek.
Lower Spool for Delete plan using Index Seek.
The subtree cost of the Table Delete and the top Table Spool does add up to the subtree cost of 1.149179, but there is no apparent explanation for how the lower Table Spool I/O and CPU costs add up to the same subtree cost.
Each Index Delete show an I/O cost of 0.0100125 and CPU cost 0.100000, slightly lower than the Table Delete I/O cost and exactly equal in CPU cost. Both Index Delete operations show a subtree cost of 1.2591919.
Upper Index Delete.
Lower Index Delete.
The last SQL operation is Sequence, which shows zero I/O cost and a CPU cost of 0.200000. Since to total subtree cost is 2.7183836, the prior operations contributed 2.5183836 of this cost, which works out to the 1.2591919 for each of the Index Delete subtrees.
It is not clear, but it may be that the costs of the operations prior to the Table Spools are dividing equally into the subtree cost of each Table Spool operation. The cost numbers do not add up exactly, but are reasonably close.
The table below shows the measured Delete times for both SARG and both execution plans. The execution plan using the index does not take into account the cost of retrieving the data row and page, but the measured costs clearly shows this is part of the actual execution plan. It is not clear why a Table Scan that deletes sequential rows takes longer than a Table Scan that deletes distributed rows. When all data is in memory, the index plan is slightly faster than the table scan. The checkpoint operation is very fast when few data pages are modified, and takes longer when all pages are modified, as would be expected.