I have two tables in my database.
TableA : 1427877 rows TableB : 339939000 rows
TableB has a foreignkey column in TableB (TableB.foreignId). It has Delete On Cascade ON.
The problem is when I delete a row in TableA.
DELETE TableA WHERE id = @id
The cascade reference, produce an index scan on TableB (300Million rows). While exits a non-clustered index on foreignId column.
I test with a procedure
CREATE OR ALTER PROCEDURE #tempDelete(@id INT) AS DELETE TableA WHERE id = @id;
After cleaning cache plan. The plan is the same, index scan.
If I did
DELETE TableB WHERE ForeignId = @id
It use an index seek on 'indexB'
I checked this post about missing index. I think the index are rigth.
Also this post about correct data type. foreignId is Int, the same that use on my .Net client and also in the store procedure.
How I could fix the Delete operation? The plan is same for production and development.
Every index on TableB have ForeignId as first column. Indexc is the only that has ForeignId as second column.