sql-server - SQL Server 中的缓慢 DELETE 语句需要帮助读取执行计划并修复

Question

我有一个删除语句违反了我的一个核心应用程序表。删除语句正在使用表的主键，但仍需要大约 30 秒。据我所知，在执行删除之前，执行计划需要在其他表中执行大约 12 次检查，其中该表是 FK。我需要帮助阅读和理解这个执行计划，才能真正知道我可以做什么来解决缓慢问题。我猜测一些索引查找或聚集索引扫描需要调整。

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)

Answer 1

确保您在其他表中的 FK 上有索引。