CODEWITHSUNDEEP
sql-serverperformancesql-server-2008-r2sql-delete
Josh M.
Josh M.

Reputation: 27811

How long should a Primary Key delete take?

Picture a simple table structure:

Table1        Table2
----------    ----------
ID<-------|   ID
Name      |-->Table1ID
              Name

Table1 has a few million rows (say 3.5 million for example). I issue a delete by Primary Key:

DELETE FROM Table1 WHERE ID = 100;

There is no row in Table2 that references Table1 with ID = 100, so the delete works without violating any Foreign Key constraints.

How long would you expect the delete to take? On the order of a few milliseconds? A few hundred milliseconds? A second or more? A few seconds? Etc., assuming the machine is not bogged down and readily handles the request.

Now, I have this situation where a delete like this is taking around 700ms. To me, this seems too slow. I'm curious if I'm off-base or if others agree this is too slow, and recommendations to help make it faster!

Here is the actual execution plan:

Execution Plan

(XML Execution plan here: http://pastebin.com/q9hSMLi3)

The Clustered Index Delete (81%) hits the Clustered PK, a Non-Clustered Unique Index, and a Non-Clustered Non-Unique Index.

Upvotes: 4

Views: 2510

Answers (2)

Martin Smith
Martin Smith

Reputation: 453648

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)).

Upvotes: 4

usr
usr

Reputation: 171216

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?

Upvotes: 1

Related Questions