求助优化涉及2000万行表的SQL存储过程执行效率

我编写的存储过程[SspArchiveDeleteReportInstance]始终无法执行完成。其中TbARIAFLD3_ARIAFLD3Errors表有2000万行数据，TbARIAFLD3Errors表有60万+行，其余表也有数万行。请协助优化该存储过程以提升执行速度，原存储过程代码如下：

ALTER PROCEDURE [dbo].[SspArchiveDeleteReportInstance]
AS
BEGIN

--------------------------------------------------
SET NOCOUNT ON
--------------------------------------------------

DECLARE @deleteTable TABLE([ReportInstanceId] uniqueidentifier NOT NULL)
Print 'Entered into the Store Procedure'
-- 关联筛选未归档的记录
INSERT @deleteTable 
    SELECT DISTINCT TOP 50 TbReportInstance.ReportInstanceId 
    FROM TbReportInstance
    INNER JOIN TbReportInstanceReportUnit ON TbReportInstanceReportUnit.ReportInstanceId = TbReportInstance.ReportInstanceId
    WHERE TbReportInstance.ArchiveFlag = 1
        
Print 'Before First Delete'
-- 删除关联的TbReportInstanceReportUnit记录
DELETE TbReportInstanceReportUnit 
FROM TbReportInstanceReportUnit
INNER JOIN @deleteTable DT ON TbReportInstanceReportUnit.ReportInstanceId = DT.ReportInstanceId

-- 处理地址文件关联数据
Print 'Before Large Statement'
DECLARE @deleteTbARIAFLD3Errors TABLE(Id int identity, ARIAFLD3ErrorId uniqueidentifier NOT NULL)
INSERT @deleteTbARIAFLD3Errors 
    select DISTINCT TbARIAFLD3Errors.ARIAFLD3ErrorId
    FROM TbARIAFLD3Errors with (nolock)
    INNER JOIN TbARIAFLD3_ARIAFLD3Errors with (nolock) ON TbARIAFLD3_ARIAFLD3Errors.ARIAFLD3ErrorId = TbARIAFLD3Errors.ARIAFLD3ErrorId
    INNER JOIN TbARIAFLD3 with (nolock) ON TbARIAFLD3.ARIAFLD3Id = TbARIAFLD3_ARIAFLD3Errors.ARIAFLD3Id
    INNER JOIN @deleteTable DT ON TbARIAFLD3.ReportInstanceId = DT.ReportInstanceId
Print '1st Large Statement Completed'

declare @count bigint, @loopCount bigint, @iterator int=1, @var uniqueidentifier
select @count=max(Id) from @deleteTbARIAFLD3Errors
select @count Counts
select @iterator = min(Id) from @deleteTbARIAFLD3Errors
--set @loopCount = @count/10000
select @iterator iterator

    WHILE (@iterator <= @count)
    BEGIN
        select top 1 @var =  ARIAFLD3ErrorId from @deleteTbARIAFLD3Errors DT where DT.Id=@iterator
        DELETE TbARIAFLD3_ARIAFLD3Errors
        FROM TbARIAFLD3_ARIAFLD3Errors where ARIAFLD3ErrorId = @var
        --INNER JOIn @deleteTbARIAFLD3Errors DT on TbARIAFLD3_ARIAFLD3Errors.ARIAFLD3ErrorId=DT.ARIAFLD3ErrorId
        --where DT.Id=@iterator
        
        set @iterator=@iterator+1       
    END
--------------------------------------------------
SET NOCOUNT OFF
--------------------------------------------------
END

1. 替换逐行循环为批量删除 #

原代码中通过WHILE循环逐行删除TbARIAFLD3_ARIAFLD3Errors数据，这在百万级表中效率极低。直接用批量删除替代：

-- 替换原循环部分
DELETE tae
FROM TbARIAFLD3_ARIAFLD3Errors tae
INNER JOIN @deleteTbARIAFLD3Errors dt ON tae.ARIAFLD3ErrorId = dt.ARIAFLD3ErrorId

如果担心单次删除数据量过大导致锁表或事务日志暴涨，可分批次删除（每次删10000条为例）：

DECLARE @RowCount INT = 1
WHILE @RowCount > 0
BEGIN
    DELETE TOP(10000) tae
    FROM TbARIAFLD3_ARIAFLD3Errors tae
    INNER JOIN @deleteTbARIAFLD3Errors dt ON tae.ARIAFLD3ErrorId = dt.ARIAFLD3ErrorId
    SET @RowCount = @@ROWCOUNT
END

2. 优化索引配置 #

• 为TbARIAFLD3_ARIAFLD3Errors表的ARIAFLD3ErrorId字段创建非聚集索引，大幅加速删除和关联查询。
• 为TbReportInstance创建ArchiveFlag + ReportInstanceId的组合索引，提升初始筛选ReportInstanceId的效率。
• 确保TbARIAFLD3的ReportInstanceId、TbReportInstanceReportUnit的ReportInstanceId字段均有索引，避免全表扫描。

3. 减少不必要的DISTINCT #

检查两处DISTINCT是否必要：

• 插入@deleteTable时，若TbReportInstance与TbReportInstanceReportUnit的关联不会产生重复ReportInstanceId，可移除DISTINCT。
• 插入@deleteTbARIAFLD3Errors时，若关联逻辑本身无重复ARIAFLD3ErrorId，也可去掉DISTINCT，避免额外的排序去重开销。

4. 合理控制事务隔离与NOLOCK使用 #

原代码中的NOLOCK可能导致脏读，若业务不允许脏读，建议移除，或开启READ COMMITTED SNAPSHOT ISOLATION减少锁冲突。

5. 调整批量处理规模 #

原代码每次仅处理50个ReportInstanceId，可根据服务器性能适当增大数值（如500或1000），减少存储过程执行次数，提升整体效率。

内容的提问来源于stack exchange，提问作者Abdul