sql - SQL SERVER 中的清除表：删除时间

Question

我负责一个 OLAP 数据库，我注意到一些清理会带来一些好处。我的第一次分析遇到了大约 50 个表中要删除的 500Millions 行。这通常占每个表的 70%。

我遇到了一些解决方案，我需要使用 tmp 表，删除原来的表，然后再将它带回来......但我有太多的依赖关系，我不想冒险走这条路。

所以我选择了另一个解决方案：一点一点地删除，所以没有表锁。

这是我在堆栈溢出中找到的代码，我试图改进删除 4000 x 4000 行。

set statistics time off

DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS 
INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '18:00:00' -- 6 PM
SET @BSTOPATMAXTIME = 1 -- ENFORCE  6 PM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS


Begin TRY

WHILE @BATCHSIZE>0
    BEGIN

-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
 IF (CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1) OR @ITERATION >2000
    BEGIN
        Return;
    END 


        DELETE top (@BATCHSIZE)
        FROM FacY where IdDimX not in (select IdDimX from  vwX)


SET @BATCHSIZE=@@ROWCOUNT
SET @ITERATION=@ITERATION+1
SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
RAISERROR (@MSG, 0, 1) WITH NOWAIT
--COMMIT TRANSACTION;

 END
end TRY 
BEGIN CATCH
 IF @@ERROR <> 0
   AND @@TRANCOUNT > 0
    BEGIN
        PRINT 'There is an error occured.  The database update failed.';
        ROLLBACK TRANSACTION;
    END;
END CATCH;

所以这大约花了。在 30 分钟到 1 小时之间删除 400 万行。

相反，我现在尝试删除 100 000 行，它在 1 分钟内完成，然后我尝试了 100 万行，它在 5-6 分钟内完成。然后我又去了 1000 万行，花了 15 分钟。（但 50GB 的日志已满 60%，所以我认为这是极限）

所以现在我想知道如果我最终删除大块不是更好吗？因为这需要很多时间。

而我不明白的是为什么删除大块需要更少的时间？

感谢您的帮助

标签： sqlsql-serverdata-cleaning