从包含120亿记录的SQL表中删除约30亿记录的有效方法

Question

这就是表的结构：

Table A
{
Id1 uniqueidentifier,
Id2 nvarchar,
Date datetime,
Details nvarchar,
Id3 uniqueidentifier
}
Clustered Index on {Id1 ASC, Details ASC, Date ASC}
Non-Clustered Index on {Date ASC}
No Foreign Key

必须删除2月的所有数据[目前该表有2月至5月的数据]每天的数据量是~100Million records，到目前为止的总数据量为~12Billion Records

正如我们最近创建的分区一样，我们只有从May开始的分区，以及all data from Feb to April are in the same partition.

这是一个高度事务性的表，我们希望在没有停机的情况下找到最好的方法。

Answer 1

这是我多年来使用的批量删除代码。它允许您设置批量大小、运行时间(夜间强制执行(等。它还将在SSMS中的消息中显示总体进度。批量删除可以减少对整体性能的影响，而且不需要停机时间。我在生产时间内在高度事务性的服务器上运行过这段代码，没有任何影响。只需确保批量大小保持在5000以下，以防止锁定。

您可能需要根据您的环境和服务器功能调整一些参数。很明显，您需要针对您的特定表名(而不是下面的SOMETABLE(以及任何其他特定内容更新此项，包括根据您的特定条件调整WHERE子句。

SET DEADLOCK_PRIORITY LOW;
DECLARE @batchSize INT           = 4000,       -- keep under 5000 to prevent locking
@waitInterval VARCHAR(8) = '00:00:10', -- wait interval between deletes
@useWaitInterval BIT     = 1,          -- turn off/on wait between deletes
@endTime VARCHAR(8)      = '08:00:00', -- 8AM
@stopAtMaxTime BIT       = 1,          -- enforce stop time
@iteration INT           = 0,          -- leave at 0
@totalRows INT           = 0,          -- leave at 0
@msg VARCHAR(500)
WHILE @batchSize > 0
BEGIN
-- if @stopAtMaxTime = 1, stop whole job at set time...
IF CONVERT(VARCHAR(8),GETDATE(),108) >= @endTime AND @stopAtMaxTime = 1
BEGIN
RETURN
END
DELETE TOP(@batchSize)
FROM SOMETABLE
WHERE 1 = 2
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
IF @useWaitInterval = 1
BEGIN
WAITFOR DELAY @waitInterval 
END
END