How Long Should A Primary Key Delete Take?
Solution 1:
The issue is the clustered index scan to validate the foreign key.
When the delete succeeds and there are no matching records that would cause a violation then all of table2 needs to be scanned. This table has 1,117,190 rows so this is an expensive operation that could definitely benefit from an index.
The 10% figure shown in the execution plan is just an estimate based on certain modelling assumptions.
The entire plan is costed at 0.0369164 with the scan on table 2 costed at 0.0036199 and everything else accounting for the remaining 0.0332965. However notice that for the clustered index scan operator the Estimated CPU Cost is 1.22907 and Estimated IO Cost is 10.7142 (totaling 11.94327 not 0.0369164).
The reason for this discrepancy is that the scan is under an anti semi join operator and the scan can stop as soon as a matching row is found. The estimated subtree cost is scaled down under the modelling assumption that this will happen after only a very small proportion of the table has been scanned.
In the case that there are no FK violations and the delete succeeds then the entire table needs to be scanned so it would be more informative to use the unscaled down figure.
If the percentages are reworked out using the 11.94327 cost for that operator that represents the full scan that happened in practice then this scan operator shows up as being 99.7% of the plan cost (11.94327 / (11.94327 + 0.0332965)).
Solution 2:
If all pages being touched are in cache you can expect about 1ms or less for the CPU cost and the log write. The client library overhead might actually be more in terms of CPU than the server load.
For each page not in cache you can expect a disk seek of 5-10ms on a magnetic disk. Roughly, you can expect one such access per index being touched in Table1 plus one access in Table2 to validate the FK.
The execution plan tells you for sure which physical ops are to be performed.
700ms seems like a lot (70 indexes?!). Please post the actual execution plan. The server is unloaded and there is no blocking due to locks?
Post a Comment for "How Long Should A Primary Key Delete Take?"