I have a delete statement that goes against one of my main application tables. The delete statement uses the primary key of the table, but it still takes about 30 seconds. As far as I can tell, the execution plan should do about 12 checks in other tables where this table is FK before deletion. I need help reading and understanding this execution plan to really find out what I can do to correct the slowness. I assume that some of the index queries or indexed index scans need to be changed.
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)