I have a delete statement that's going against one of my core application tables. The delete statement is using the table's primary key but is still taking around 30 seconds. As far as I can tell the execution plan needs to do about 12 checks in other tables where this table is a FK prior to doing the delete. I need help reading and understanding this execution plan to truly know what I can do to fix the slowness. I'm guessing some of the index seeks or clustered index scans need to be tweaked.
StmtText
---------------------------------------------
delete from Clean where CleanId = 17526195
(1 row(s) affected)
StmtText
--------
|--Assert(WHERE:(CASE WHEN NOT [Expr1042] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1043] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1044] IS NULL THEN (2) ELSE CASE WHEN NOT [Expr1045] IS NULL THEN (3) ELSE CASE WHEN NOT [Expr1046] IS NULL THEN (4) ELSE CA
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1053] = [PROBE VALUE]))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1052] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1051] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1050] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1049] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1048] = [PROBE VALUE]))
| | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1047] = [PROBE VALUE]))
| | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1046] = [PROBE VALUE]))
| | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1045] = [PROBE VALUE]))
| | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1044] = [PROBE VALUE]))
| | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1043] = [PROBE VALUE]))
| | | | | | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([TcaNetMigrated].[dbo].[Clean].[CleanId]), DEFINE:([Expr1042] = [PROBE VALUE]))
| | | | | | | | | | | |--Clustered Index Delete(OBJECT:([TcaNetMigrated].[dbo].[Clean].[PK_Clean]), OBJECT:([TcaNetMigrated].[dbo].[Clean].[_IX_Clean_CustomerID_CleanID]), OBJECT:([TcaNetMigrated].[dbo].[Clean].
| | | | | | | | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Breakage].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[Breakage].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | | | | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Cancellation].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[Cancellation].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Seek(OBJECT:([TcaNetMigrated].[dbo].[CleanEmployee].[PK_CleanEmployee]), SEEK:([TcaNetMigrated].[dbo].[CleanEmployee].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FO
| | | | | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[CleanTransaction].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[CleanTransaction].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Complaint].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[Complaint].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Complaint].[IX_Complaint_RedoCleanId]), SEEK:([TcaNetMigrated].[dbo].[Complaint].[RedoCleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[GreatJob].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[GreatJob].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Inspection].[IX_Inspection_CleanId_InspectionId]), SEEK:([TcaNetMigrated].[dbo].[Inspection].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([TcaNetMigrated].[dbo].[FranchiseCall].[PK_FranchiseCalls]), WHERE:([TcaNetMigrated].[dbo].[FranchiseCall].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]))
| | |--Clustered Index Scan(OBJECT:([TcaNetMigrated].[dbo].[IVRLog].[PK_IVRLog]), WHERE:([TcaNetMigrated].[dbo].[IVRLog].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]))
| |--Index Seek(OBJECT:([TcaNetMigrated].[dbo].[Lockout].[IX_UniqueCleanId]), SEEK:([TcaNetMigrated].[dbo].[Lockout].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([TcaNetMigrated].[dbo].[ManualUpdateTime].[PK_ManualUpdateTimes]), WHERE:([TcaNetMigrated].[dbo].[ManualUpdateTime].[CleanId]=[TcaNetMigrated].[dbo].[Clean].[CleanId]))
(26 row(s) affected)
