How long should the primary key be removed?

Question

Draw a simple table structure:

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

Table1 has several million rows (e.g. 3.5 million). I am doing the deletion using the main key:

 DELETE FROM Table1 WHERE ID = 100; 

Table2 does not have a row that refers to Table1 with ID = 100 , so deleting works without violating any foreign key restrictions.

How long can you expect removal? A few milliseconds in order? A few hundred milliseconds? Second or more? Few seconds? Etc., Assuming that the machine is not bogged down and readily processes the request.

Now I have a situation where a deletion like this takes about 700 ms. For me it seems too slow. I am curious if I am out of base or if others agree that it is too slow, and recommendations to help make it faster!

Here is the actual execution plan:

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

Clustered Index Delete (81%) ends up in a cluster PC, a nonclustered unique index and a nonclustered non-unique index.

Answer 1

The problem is clustered index scanning to verify the foreign key.

When the deletion succeeds and there are no corresponding entries that could lead to a violation, it is necessary to check all table2 . This table has 1,117,190 rows, so this is an expensive operation that can definitely benefit from the index.

The 10% score shown in the execution plan is simply an estimate based on some modeling assumptions.

The entire plan is estimated at 0.0369164 when scanned in table 2, the cost of which is 0.0036199 , and everything else takes into account the remaining 0.0332965 . However, note that for the operator of the clustered scan index, the estimated cost of the CPU is 1.22907 and the estimated cost of I / O is 10.7142 (the total amount of 11.94327 not 0.0369164 ).

The reason for this discrepancy is that the scan is under the anti semi join statement, and the scan may stop as soon as the corresponding row is found. The estimated cost of the subtree is reduced in accordance with the modeling assumption that this will happen after only a very small part of the table has been viewed.

In case there are no FK violations and the deletion succeeds, then the whole table should be scanned, so it would be more informative to use an unscaled snapshot.

If the interest is processed using the cost of 11.94327 for this operator, which represents the full scan that has occurred in practice, then this scan operator is displayed as 99.7% of the cost of the plan ( 11.94327 / (11.94327 + 0.0332965) ).

Answer 2

If all the affected pages are in the cache, you can expect about 1 ms or less for processor cost and logging. The overhead of the client library may be more likely from the point of view of the processor than to load the server.

For each page that is not in the cache, you can expect the disk to search for 5-10 ms on the magnetic disk. Roughly speaking, you can expect that one such access to the index will be affected in Table1 plus one access in Table2 to check for FK.

In terms of execution, you are sure to verify what physical operations should be performed.

700 ms seems a lot (70 indexes ?!). Submit the actual execution plan. Is the server unloaded and locks not blocked?