时间戳

首页 > 解决方案 > 从包含 120 亿条记录的 SQL 表中删除约 30 亿条记录的有效方法

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

问题描述

这就是表格的结构：

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.

这是一个高度事务性的表，我们希望在不停机的情况下做到这一点的最佳方法。

标签： sqlsql-servertsqlsql-server-2012

解决方案

这是我多年来使用的批量删除代码。它允许您设置批处理大小、运行时间（隔夜强制执行）等。它还将在 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

推荐阅读