Delete cascade produce scan intead of index seek

Question

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'

Plan for both query

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.

Thanks,

Every index on TableB have ForeignId as first column. Indexc is the only that has ForeignId as second column.

Answer 1

You may want to look in the table sys.foreign_keys for the foreign key joining the tables, and see if the index has the value is_not_trusted = 1.

This query, originally from BrentOzar.com, will show you which foreign keys in your system are not trusted:

SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0;
GO
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname
from sys.check_constraints i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;
GO

Note: I recommend at least subscribing to Brent Ozar's emails - lots of interesting information coming out of that website, and their Blitz queries are really helpful.

Answer 2

I had a similar issue - for me it was because the foreign key index was marked as non-unique.

Presumably the query needs to do a scan to find multiple rows that match the foreign key, rather than just one?

Anyway, updating the foreign key's index to be unique changed the delete from being a scan to a seek for me.