I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables.
Two of the tables (Result, Amount) have about 90 million rows. They are almost identical in nature. They both have:
- A composite PK and a single decimal 'Value' column.
- Both are joining tables where Period is one side of the relationship.
- In addition to the PK clustered index, both have a NC index based on PeriodId and including the 'Value' column.
SELECTs that filter on PeriodId are very fast. I find however that DELETEs take too long (2-3 seconds) and that the main culprit is the Amount table.
I can't understand, given their identical setups, why one table takes up ~75% of the DELETE time. You can see from the picture of the query plan that the culprits are the two sorts. What would cause the Result table to DELETE so quickly, but the AMOUNT table to use these 'SORT's?
[Part of the query plan showing the RESULT delete on the top row and the AMOUNT delete on the bottom. Igore the middle part]