清洗作业优化

Question

Server 2008 R2企业

我有一个有3个表的数据库，保留时间为15天。这是一个日志数据库，非常活跃，大小约500 GB，每天消耗约30 GB，除非清除。我似乎不能陷入其中的一张桌子上，我落后了。该表有2.2亿行，需要每晚清除大约1,000万至1,200万行。我现在需要清除3000万行。我只能在夜间运行此清除，因为输入插入的数量与表锁竞争。我已经确认了所有的索引都是正确的，并且运行了布伦特·奥扎尔的sp_Blitz_Index来确认这一点。有什么方法可以优化我下面做的事情吗？我对每个表运行相同的清除步骤。

1. 删除并创建3个清除表: Purge_Log、Purge_SLogHeader和Purge_SLogMessage。

2.在清除表中插入行(每个表需要5分钟)：

Insert Into Purge_Log
Select ID from ServiceLog 
where startTime <  dateadd (day, -15, getdate()  )

--****************************************************

Insert into Purge_SLogMessage
select serviceLogId from ServiceLogMessage 
where serviceLogId in ( select id from 
                       ServiceLog 
                       where startTime <  dateadd (day, -15, getdate() ))

--****************************************************

Insert into Purge_SLogHeader
Select serviceLogId from ServiceLogHeader
where serviceLogId in ( select id from 
                       ServiceLog 
                       where startTime <  dateadd (day, -15, getdate()  ))

插入之后，我对每个表执行以下不同的操作：

SET ROWCOUNT 1000

delete_more:
delete from ServiceLog
where Id in ( select Id from Purge_Log)

IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0     

基本上，有没有人认为我可以让这个过程运行得更快，或者有一个不同的方法去做。我已经使查询尽可能简单，并且只有一个子查询。我使用了一个join，执行查询计划说，以这种方式完成它的时间是一样的。如有任何指导，将不胜感激。

Answer 1

您可以对所有表使用这种技术，首先在临时表中收集ID，以避免在大量数据中一次又一次地扫描原始表。我希望它能很好地适用于你所有的桌子：

DECLARE @del_query VARCHAR(MAX)

/*从ServiceLog表而不是Purge_Log获取ID，因为Purge_Log可能有比预期更多的数据，因为频繁地清除*/

IF OBJECT_ID('tempdb..#tmp_log_ids') IS NOT NULL DROP TABLE #tmp_log_ids
SELECT ID INTO #tmp_log_ids FROM ServiceLog WHERE startTime < DATEADD(DAY, -15, GETDATE())

SET @del_query ='
DELETE TOP(100000) sl
FROM ServiceLog sl 
INNER JOIN #tmp_log_ids t ON t.id = s1.id'
WHILE 1 = 1
BEGIN
    EXEC(@del_query + ' option(maxdop 5) ')
    IF @@rowcount < 100000 BREAK;
END 

SET @del_query ='
DELETE TOP(100000) sl
FROM ServiceLogMessage sl 
INNER JOIN #tmp_log_ids t ON t.id = s1.serviceLogId'
WHILE 1 = 1
BEGIN
    EXEC(@del_query + ' option(maxdop 5) ')
    IF @@rowcount < 100000 BREAK;
END

SET @del_query ='
DELETE TOP(100000) sl
FROM ServiceLogHeader sl 
INNER JOIN #tmp_log_ids t ON t.id = s1.serviceLogId'
WHILE 1 = 1
BEGIN
    EXEC(@del_query + ' option(maxdop 5) ')
    IF @@rowcount < 100000 BREAK;
END