Search code examples
sql-serverforeign-keyssql-deletesql-execution-plansqlperformance

SQL Delete a single row by PK is very slow

In our app, we can undo the creation of a Task just created. As an example we would issue the following delete:

delete from task where taskid = 290313

This is very slow and can take up to 30 seconds to execute.

Dozens of tables have a foreign key to Task, and TaskId is indexed in all of them.

Looking at the Execution Plan, I can see many Index Scan (non Seek) that reads all records in the index. enter image description here

Why is it using a full Index Scan instead of Seek ?

I'm on Azure SQL Database latest version.

[EDIT]

Indexes on Task table:

enter image description here

Query plan here: https://www.brentozar.com/pastetheplan/?id=SJtzfNyp7

Here is the result of a Live Query Statistics, we clearly see the time lost to Index Scan: enter image description here

Solution

  • I dropped and re-created all Foreign Keys with NO cascade delete. Now the execution plan is using efficient Index Seeks to check RI on all FKs.

    Not sure why having cascade delete enabled caused these Index Scans though...