patternsqlModerate
SQL estimates are way off on DELETE statement with Triggers on huge tables
Viewed 0 times
estimatestablesdeletestatementsqlarewithwayhugetriggers
Problem
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:
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. T
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. T
Solution
Assuming all the related tables have correct indexing for the delete paths, you could try:
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.
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.
Code Snippets
DELETE [Trips]
WHERE [ISAFileName]='ID_774199_20200311_133117.isa'
OPTION (LOOP JOIN, FAST 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));Context
StackExchange Database Administrators Q#262111, answer score: 17
Revisions (0)
No revisions yet.