SQL estimates are way off on DELETE statement with Triggers on huge tables 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
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.