관련 질문에 답변했을 때이 질문에 대해 알지 못했지만 ( 이 while 루프에 명시 적 트랜잭션이 필요합니까? ) 완전성을 위해이 링크 된 답변에 대한 제안의 일부가 아니기 때문에이 문제를 여기서 다루겠습니다. .

SQL 에이전트 작업 (결국 1 억 행)을 통해이를 예약하도록 제안했기 때문에 클라이언트 (예 : SSMS)에 상태 메시지를 보내는 어떤 형식이 이상적이라고 생각하지 않습니다. 다른 프로젝트가 필요하다면 Vladimir에 동의하는 RAISERROR('', 10, 1) WITH NOWAIT;것이 좋습니다.)

이 특별한 경우에는 지금까지 업데이트 된 행 수로 각 루프마다 업데이트 할 수있는 상태 테이블을 만듭니다. 그리고 현재 시간을내어 프로세스에 심장 박동을 가하는 것은 아프지 않습니다.

프로세스를 취소했다가 다시 시작하려는 경우, 기본 테이블의 UPDATE를 명시 적 트랜잭션의 상태 테이블 UPDATE로 래핑하는 것이 지겨워 요. 그러나 취소로 인해 상태 테이블이 동기화되지 않았다고 생각되면 단순히로 수동으로 업데이트하여 현재 값으로 쉽게 새로 고칠 수 있습니다 COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL.그리고 업데이트 할 두 개의 테이블 (예 : 기본 테이블과 상태 테이블)이 있습니다. 우리 는 명시 적 트랜잭션을 사용하여 두 테이블을 동기화 상태로 유지해야하지만 프로세스를 취소하면 고아 트랜잭션이 발생할 위험이 없습니다. 트랜잭션이 시작되었지만 커밋하지 않은 후. SQL 에이전트 작업을 중지하지 않는 한 안전합니다.

음, 프로세스를 중지하지 않고 어떻게 프로세스를 중지 할 수 있습니까? 중지하도록 요청하여 :-). 네. 프로세스를 “신호”( kill -3유닉스 와 유사)로 보내면 다음 편리한 순간 (즉, 활성 트랜잭션이 없을 때)에서 중지하고 모든 깔끔하고 깔끔한 정리를 할 수 있습니다.

다른 세션에서 실행중인 프로세스와 어떻게 통신 할 수 있습니까? 현재 상태를 사용자에게 다시 전달하기 위해 만든 것과 동일한 메커니즘을 사용하여 상태 테이블. 프로세스가 진행 또는 중단 여부를 알 수 있도록 각 루프의 시작 부분에서 프로세스가 확인할 열을 추가하기 만하면됩니다. 그리고이를 10 분 또는 20 분마다 실행하는 SQL 에이전트 작업으로 예약하려는 것이기 때문에 프로세스가 진행중인 경우 임시 테이블을 백만 행으로 채울 때 아무런 요점이 없으므로 맨 처음부터 확인해야합니다. 잠시 후 종료하고 해당 데이터를 사용하지 마십시오.

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. #CurrentSet임시 테이블에 PRIMARY KEY를 추가하십시오 . 여기서 아이디어는 옵티마이 저가 1 억 행 테이블에 JOIN을 사용하도록 돕는 것입니다.

   그리고 모호하지 않도록 언급하기 위해 PK를 #FullSet주문과 관련이없는 간단한 대기열 테이블이므로 임시 테이블에 PK를 추가 할 이유가 없어야합니다 .

3. 경우에 따라 필터링 된 인덱스를 추가 SELECT하여 #FullSet임시 테이블 로의 피드 를 지원하는 데 도움이 됩니다. 이러한 인덱스 추가와 관련된 몇 가지 고려 사항은 다음과 같습니다.
   1. WHERE 조건은 쿼리의 WHERE 조건과 일치해야합니다. WHERE deleted is null or deletedDate is null
   2. 프로세스가 시작될 때 대부분의 행은 WHERE 조건과 일치하므로 인덱스가 도움이되지 않습니다. 이것을 추가하기 전에 50 % 정도가 될 때까지 기다릴 수 있습니다. 물론, 인덱스를 추가하는 것이 얼마나 도움이되고 언제 가장 좋은지 몇 가지 요인에 따라 달라 지므로 시행 착오가 조금 발생합니다.
   3. 기본 데이터가 자주 변경되기 때문에 인덱스를 수동으로 업데이트 및 / 또는 인덱스를 최신 상태로 유지해야 할 수도 있습니다.
   4. 을 돕는 동안 인덱스 는 작업 중에 업데이트해야하는 또 다른 개체이므로 더 많은 I / O를 수행하므로 인덱스 SELECT가 손상 UPDATE될 수 있습니다. 이것은 필터링 된 인덱스 (필터와 일치하는 행이 적기 때문에 행을 업데이트함에 따라 줄어 듭니다)와 인덱스를 추가하기 위해 잠시 기다립니다 (처음에 큰 도움이되지 않는 경우 발생하지 않을 이유) 추가 I / O).

답변

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

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

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