SQL estimates are way off on DELETE statement with Triggers on huge tables

Question

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

This server is on SSD drives and has a max memory of 128 gb. CostTheshold for Parallelism is 70, MaxDegree of Parallelism is 3.

I have a "Trips" table which is referenced by 23 foreign keys with the ON DELETE CASCADE option.

This table by itself is not that big (5.3 millions rows, 1.3 gb of data). But of the 23 referenced tables, two of the tables are quite big (more than 1 billions rows, 54 and 69 gb each).

The problem is when we try to delete a small amount of rows in the "Trips" table (let's say 4 rows), SQL estimates so much rows are going to be deleted, it asks for 10gb of RAM, estimates millions of rows will be returned, and locks the table. All goes to a halt and other queries block and the application time outs.

Here are the main tables and the row count for 1 delete statement:

• Trips (4 rows)
• Segments (27 rows, related to Trips by SegmentId)
• Profiles (2012 rows, related to Segments by SegmentId)
• ProfileRanges (2337 rows, related to Profiles by ProfileId)
• Events (7750 rows, related to Segments by SegmentId)
• EventConditions (9230 rows, related to Events by EventId)

Tables EventConditions and ProfileRanges each have more than 1 billion of rows.

Here is the plan cache : https://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

When I look in SentryOne plan explorer, I can see that SQL is reading the whole table even if the "Table spool" then filters and keeps only for 2012 rows ProfileRanges and about the same for EventConditions.

When I look at the memory grant of the query with Brent Ozar's sp_blitzCache procedure, I can see that the query asks for about 10gb of RAM.

After that, the query is either waiting on SOS_SCHEDULER_YIEL (so waiting for it's turn to use the CPU after the 4ms) or MEMORY_ALLOCATION_EXT. The program times out and fails.

What can I do to make this work?

One of the thing I was thinking of, was removing the foreign keys on the two biggest table and delete their rows in an instead of trigger. But I'm not a big fan of enforcing database consistency with triggers instead of foreign keys.

Any advice or help will be appreciated

---

Primary Key of ProfileRanges is

• ProfileId int
• ProfileRangeDefId1 int
• ProfileRangeDefId2 int

Primary key of EventConditions is

• EventId bigint
• EventConditionDefId int

Answer 1

Assuming all the related tables have correct indexing for the delete paths, you could try:

DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

If that works, try to reduce it to the minimal number of hints.

These sorts of plans are very challenging for cardinality estimation, and the 'default' CE model often makes a mess.

Once you have a plan shape that works well, you should be able to force that shape using a plan guide etc. if necessary.

Answer 2

A table scan on cascade delete is a common symptom of not having proper indexes on your tables.

Ensure that all of the FK tables have indexes supporting the foreign keys. IE a clustered or non-clustered index with the FK column(s) as the leading column(s) in the index of the other tables.

EG

create index ix_TripId on EventConditions (TripId)

And consider whether the TripID FK column shouldn't be the leading column in the Clustered Index, eg:

create table EventConditions
(
  TripId int not null,
  EventId bigint not null,
  EventConditionDefId int not null,
  constraint pk_EventConditions 
     primary key clustered(TripId, EventId, EventConditionDefId),
  ...
)

This would optimize each table for access by TripId.

Additionally

One of the thing I was thinking of, was removing the foreign keys on the two biggest table and delete their rows in an instead of trigger.

You don't need to remove the FKs. Just delete from the child tables first, and possibly remove ON DELETE CASCADE from the FK to require deleting the child tables first. Which would start with loading temp tables with the key values to be deleted at each level, and loading them from the top down.

create table #tripIdsToDelete(TripId int primary key)
insert into #tripIdsToDelete ...

create table #EventIdsToDelete(EventId int primary key)
insert into #EventIdsToDelete(EventID)
  select EventId from Events 
  where TripId in (select TripId from #tripIdsToDelete)
...
create table #EventConditionIdsToDelete ...