0

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.

TableB index

3

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.

  • 1
    Full disclosure: I approve of this advice – Erik Darling Sep 14 '17 at 23:17
  • 1
    I'm not so sure, that company is kinda shady. – Brent Ozar Sep 14 '17 at 23:18
  • Hi, thanks. That show they are untrusted in production. I have to run this on weekend, i'll show the result on monday. Anyway, in development, is_not_trusted = 0 for all of them. – Serafín Sep 15 '17 at 9:35
  • link BULK INSERT is marking untrusted de column again and again. I have to change that code too. – Serafín Sep 17 '17 at 8:10
  • I update builk insert to enforce check constraint. Foreign key are trusted. But the plan is the same with scan. I used "OPTION RECOMPILE" but it produce the same plan. – Serafín Sep 17 '17 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.