WEEK 03 - SLOW FK CHECK ON DELETE

No Trigger, No Locks, No Extra Indexes, No Child Rows

This week I looked at a DELETE that looked harmless on paper.

One row. Filtered by the primary key. No trigger. No locks. No redundant indexes on the table. No child rows pointing at it.

It still took about 38 seconds and 11.4 million logical reads, and kept showing up near the top of the workload

THE SYMPTOM

The application ran:

DELETE FROM dbo.Cities
WHERE CityID = @CityID;

Nothing about it screamed trouble:

one row, filter on the PK
no trigger on the table
no locks, no blocking in sp_whoisactive
no pile of overlapping indexes on Cities
no matching rows in Patients, Doctors, Reservations, or Locations

So why 38 seconds and 11.4M reads?

Waits were clean. Blocking wasn't the story. I opened the actual execution plan…

THE EXECUTION PLAN

The DELETE on Cities wasn't where the time went.

The plan showed Clustered Index Scan on every child table tied to a foreign key.

four clustered scans under the DELETE, one per child FK

Plan area	Meaning
Delete on parent	The one row leaving Cities
Scan on child table	FK check “does anything still reference this key?”
No seek on CityID	No index on the referencing column
High logical reads	Almost all cost is in the checks, not the delete

Nested Loops (Left Semi Join) between the scans. Assert at the end.

You won't see a missing-index hint on Cities. Correct, the parent table was fine.

THE PATTERN

When a parent DELETE is slow and you've ruled out blocking:

Open the actual execution plan.
Look for Scan operators on child tables in the DELETE plan.
Verify whether the FK columns are indexed.

Scans while hunting for rows that aren't there usually mean missing FK reinforcement indexes.

THE ROOT CAUSE

Cities had a proper primary key.

The child tables had foreign keys on CityID.
What they didn't have was a nonclustered index on CityID.

Before SQL Server removes a parent key, it has to prove no child row still references it. No index on the child side → a full table scan (clustered index scan) per FK.

The city I was deleting had zero child rows. That didn't skip the check. SQL Server still had to scan Patients, Doctors, Reservations, and Locations to confirm it.

Four tables. Four scans. One row deleted.

In production there were more than four child tables. Same pattern on each FK without a supporting index.

THE FIX

One narrow index per FK column on the child, not the parent:

CREATE NONCLUSTERED INDEX IX_Patients_CityID
    ON dbo.Patients (CityID);

CREATE NONCLUSTERED INDEX IX_Doctors_CityID
    ON dbo.Doctors (CityID);

CREATE NONCLUSTERED INDEX IX_Reservations_CityID
    ON dbo.Reservations (CityID);

CREATE NONCLUSTERED INDEX IX_Locations_CityID
    ON dbo.Locations (CityID);

That was the whole fix, four indexes on the child side.
The DELETE in the application stayed exactly as it was.

After that, each FK check became a quick Index Seek instead of a slow Clustered Index Scan.

Index Seek on each child table; the DELETE cost drops to a handful of reads

THE NUMBERS

Metric	Before	After
Rows deleted	1	1
Child rows found	0	0
Elapsed time	~38 s	< 50 ms
Logical reads	~11.4 M	~48
Child-table access	Clustered Scan × 4	Index Seek × 4

The DELETE in the app didn't change.
What changed was how SQL Server checked the foreign keys and the read count.

UNDER THE HOOD

Foreign keys protect referential integrity. On DELETE or UPDATE of a parent key, SQL Server must verify no child still points at that value.

Index on the child FK column → seek, stop when the answer is clear.

No index → scan the whole child table.

That runs even when the answer is "zero rows." Proving absence at scale is not free.

Same indexes help INSERT/UPDATE on the child and parent changes too. The delete-with-no-children case is just the one that fools you when blocking dashboards look fine.

WATCH OUT

Don't drop FKs to speed up deletes. You lose integrity; you don't fix the access path.
Put the reinforcement index on the child FK column, not on the parent PK.
Already have a composite index leading with CityID?
You may be covered - check before adding another.
More indexes = slightly heavier writes on child tables.
On large FK-backed tables, the tradeoff is usually worth it.

THE LESSON

The time went into proving the delete wouldn't break a foreign key, and without indexes on the child side, that proof meant scanning every child table, every time.

Add the FK reinforcement indexes. Keep the constraints. The DELETE stays one line.

Pier Giuseppe Mandosi
SQL Server Performance Diaries

One Record Delete by PK