sql server：以小块更新大表上的字段：如何获取进度/状态？

当我回答相关问题时，我不知道这个问题（在这个 while 循环中需要显式事务吗？），但为了完整起见，我将在这里解决这个问题，因为它不是我在该链接答案中的建议的一部分.

因为我建议通过 SQL 代理作业来安排这个（毕竟它是 1 亿行），所以我认为任何形式的向客户端（即 SSMS）发送状态消息都不是理想的（尽管如果那是曾经需要其他项目，那么我同意弗拉基米尔的观点，即使用RAISERROR('', 10, 1) WITH NOWAIT;是要走的路）。

在这种特殊情况下，我将创建一个状态表，可以在每个循环中更新迄今为止更新的行数。投入当前时间来了解这个过程并没有什么坏处。

鉴于您希望能够取消并重新启动该过程，我厌倦了在显式事务中将主表的 UPDATE 与状态表的 UPDATE 包装起来。但是，如果您觉得由于取消而导致状态表不同步，只需使用COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL.并且有两个要更新的表（即主表和状态表），我们应该使用显式事务来保持这两个表同步，但是如果您取消进程，我们不希望冒孤立事务的风险点在它开始事务但尚未提交之后。只要您不停止 SQL 代理作业，这应该是安全的。

你怎么能停止这个过程而不，嗯，停止它？通过要求它停止:-)。是的。通过向进程发送一个“信号”（类似于kill -3在 Unix 中），您可以请求它在下一个方便的时刻停止（即，当没有活动的事务时！）并让它自己清理干净整洁。

您如何与另一个会话中正在运行的进程进行通信？通过使用我们为其创建的相同机制将其当前状态传达给您：状态表。我们只需要添加一个列，该进程将在每个循环开始时检查它，以便它知道是继续还是中止。而且由于目的是将其安排为 SQL 代理作业（每 10 或 20 分钟运行一次），我们还应该在一开始就检查，因为如果进程刚刚开始，用 100 万行填充临时表是没有意义的稍后退出并且不使用任何数据。

DECLARE @BatchRows INT = 1000000,
        @UpdateRows INT = 4995;

IF (OBJECT_ID(N'dbo.HugeTable_TempStatus') IS NULL)
BEGIN
  CREATE TABLE dbo.HugeTable_TempStatus
  (
    RowsUpdated INT NOT NULL, -- updated by the process
    LastUpdatedOn DATETIME NOT NULL, -- updated by the process
    PauseProcess BIT NOT NULL -- read by the process
  );

  INSERT INTO dbo.HugeTable_TempStatus (RowsUpdated, LastUpdatedOn, PauseProcess)
  VALUES (0, GETDATE(), 0);
END;

-- First check to see if we should run. If no, don't waste time filling temp table
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
  PRINT 'Process is paused. No need to start.';
  RETURN;
END;

CREATE TABLE #FullSet (KeyField1 DataType1, KeyField2 DataType2);
CREATE TABLE #CurrentSet (KeyField1 DataType1, KeyField2 DataType2);

INSERT INTO #FullSet (KeyField1, KeyField2)
  SELECT TOP (@BatchRows) ht.KeyField1, ht.KeyField2
  FROM   dbo.HugeTable ht
  WHERE  ht.deleted IS NULL
  OR     ht.deletedDate IS NULL

WHILE (1 = 1)
BEGIN
  -- Check if process is paused. If yes, just exit cleanly.
  IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
  BEGIN
    PRINT 'Process is paused. Exiting.';
    BREAK;
  END;

  -- grab a set of rows to update
  DELETE TOP (@UpdateRows)
  FROM   #FullSet
  OUTPUT Deleted.KeyField1, Deleted.KeyField2
  INTO   #CurrentSet (KeyField1, KeyField2);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RAISERROR(N'All rows have been updated!!', 16, 1);
    BREAK;
  END;

  BEGIN TRY
    BEGIN TRAN;

    -- do the update of the main table
    UPDATE ht
    SET    ht.deleted = 0,
           ht.deletedDate = '2000-01-01'
    FROM   dbo.HugeTable ht
    INNER JOIN #CurrentSet cs
            ON cs.KeyField1 = ht.KeyField1
           AND cs.KeyField2 = ht.KeyField2;

    -- update the current status
    UPDATE ts
    SET    ts.RowsUpdated += @@ROWCOUNT,
           ts.LastUpdatedOn = GETDATE()
    FROM   dbo.HugeTable_TempStatus ts;

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
      ROLLBACK TRAN;
    END;

    THROW; -- raise the error and terminate the process
  END CATCH;

  -- clear out rows to update for next iteration
  TRUNCATE TABLE #CurrentSet;

  WAITFOR DELAY '00:00:01'; -- 1 second delay for some breathing room
END;

-- clean up temp tables when testing
-- DROP TABLE #FullSet; 
-- DROP TABLE #CurrentSet; 

然后，您可以随时使用以下查询检查状态：

SELECT sp.[rows] AS [TotalRowsInTable],
       ts.RowsUpdated,
       (sp.[rows] - ts.RowsUpdated) AS [RowsRemaining],
       ts.LastUpdatedOn
FROM sys.partitions sp
CROSS JOIN dbo.HugeTable_TempStatus ts
WHERE  sp.[object_id] = OBJECT_ID(N'ResizeTest')
AND    sp.[index_id] < 2;

想要暂停进程，无论它是在 SQL 代理作业中运行，还是在其他人计算机上的 SSMS 中运行？赶紧跑：

UPDATE ht
SET    ht.PauseProcess = 1
FROM   dbo.HugeTable_TempStatus ts;

希望该过程能够重新启动吗？赶紧跑：

UPDATE ht
SET    ht.PauseProcess = 0
FROM   dbo.HugeTable_TempStatus ts;

更新：

这里有一些额外的尝试可能会提高此操作的性能。没有一个可以保证有帮助，但可能值得测试。并且有 1 亿行要更新，您有足够的时间/机会来测试一些变化 ;-)。

1. 添加TOP (@UpdateRows)到 UPDATE 查询中，使第一行看起来像：
   UPDATE TOP (@UpdateRows) ht
   有时它可以帮助优化器了解最大行数将受到影响，因此它不会浪费时间寻找更多。

2. 将 PRIMARY KEY 添加到#CurrentSet临时表。这里的想法是帮助优化器对 1 亿行表进行 JOIN。

   并且只是为了不产生歧义，不应该有任何理由将 PK 添加到#FullSet临时表，因为它只是一个简单的队列表，其中顺序无关紧要。

3. 在某些情况下，它有助于添加过滤索引以帮助将SELECT其馈送到#FullSet临时表中。以下是与添加此类索引相关的一些注意事项：
   1. WHERE 条件应与查询的 WHERE 条件匹配，因此WHERE deleted is null or deletedDate is null
   2. 在该过程开始时，大多数行将匹配您的 WHERE 条件，因此索引没有那么有用。您可能要等到 50% 左右再添加它。当然，它有多大帮助以及何时最好添加索引会因多种因素而异，因此需要反复试验。
   3. 您可能必须手动 UPDATE STATS 和/或 REBUILD 索引以使其保持最新，因为基础数据经常更改
   4. 请务必记住，索引在帮助 的同时SELECT会伤害 ，UPDATE因为它是在该操作期间必须更新的另一个对象，因此需要更多的 I/O。这既适用于使用过滤索引（当您更新行时会缩小，因为与过滤器匹配的行较少），并等待一段时间添加索引（如果它在开始时不会很有帮助，那么没有理由招致额外的 I/O）。

回答第二部分：如何在循环期间打印一些输出。

我很少有系统管理员有时必须运行的长期维护程序。

我从 SSMS 运行它们，还注意到该PRINT语句仅在整个过程完成后才显示在 SSMS 中。

所以，我使用RAISERROR的是低严重性：

DECLARE @VarTemp nvarchar(32);
SET @VarTemp = CONVERT(nvarchar(32), GETDATE(), 121);
RAISERROR (N'Your message. Current time is %s.', 0, 1, @VarTemp) WITH NOWAIT;

我正在使用 SQL Server 2008 Standard 和 SSMS 2012 (11.0.3128.0)。这是在 SSMS 中运行的完整工作示例：

DECLARE @VarCount int = 0;
DECLARE @VarTemp nvarchar(32);

WHILE @VarCount < 3
BEGIN
    SET @VarTemp = CONVERT(nvarchar(32), GETDATE(), 121);
    --RAISERROR (N'Your message. Current time is %s.', 0, 1, @VarTemp) WITH NOWAIT;
    --PRINT @VarTemp;

    WAITFOR DELAY '00:00:02';
    SET @VarCount = @VarCount + 1;
END

当我注释掉RAISERROR并仅在 SSMS 的“消息”选项卡中留下PRINT消息时，仅在 6 秒后整个批次完成后才会出现。

当我注释掉PRINT并使用RAISERRORSSMS 中的消息选项卡中的消息时，无需等待 6 秒，而是随着循环的进行。

有趣的是，当我同时使用RAISERRORand时PRINT，我看到了两条消息。首先来自 first 的消息RAISERROR，然后延迟 2 秒，然后是 firstPRINT和 second RAISERROR，依此类推。

在其他情况下，我使用一个单独的专用log表，并简单地在表中插入一行，其中包含一些描述长期运行进程的当前状态和时间戳的信息。

在漫长的过程中，我会定期从桌子上SELECT查看log发生了什么。

这显然有一定的开销，但它会留下一个日志（或日志历史记录），我以后可以按照自己的进度检查。

您可以从另一个连接监视它，例如：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT COUNT(*) FROM [huge-table] WHERE deleted IS NULL OR deletedDate IS NULL 

看看还有多少要做。如果应用程序正在调用进程，而不是您在 SSMS 或类似程序中手动运行它，并且需要显示进度，这可能很有用：异步运行主进程（或在另一个线程上），然后循环调用“还剩多少" 每隔一段时间检查一次，直到异步调用（或线程）完成。

将隔离级别设置得尽可能宽松意味着这应该在合理的时间内返回，而不会由于锁定问题而被卡在主事务后面。当然，这可能意味着返回的值有点不准确，但作为一个简单的进度表，这根本不重要。