SQL Server : 작은 덩어리로 거대한 테이블의 필드 업데이트 : 진행 상황 / 상태를 얻는 방법? 매우 큰 (1 억 행) 테이블을 가지고

우리는 매우 큰 (1 억 행) 테이블을 가지고 있으며 테이블에 몇 개의 필드를 업데이트해야합니다.

로그 배송 등을 위해, 우리는 또한 한입 규모의 거래를 유지하기를 원합니다.

  • 아래는 트릭을 수행합니까?
  • 어떻게 출력물을 인쇄 할 수있게됩니까? 진행 상황을 볼 수 있습니까? (우리는 거기에 PRINT 문을 추가하려고 시도했지만 while 루프 중에 아무것도 출력되지 않았습니다)

코드는 다음과 같습니다

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END


답변

관련 질문에 답변했을 때이 질문에 대해 알지 못했지만 ( 이 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).

답변

두 번째 부분에 응답 : 루프 중에 일부 출력을 인쇄하는 방법.

sys admin이 때때로 실행해야하는 장기 실행 유지 관리 절차가 거의 없습니다.

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만 남겨 PRINTSSMS에서 메시지 탭의 메시지를 전체 일괄 처리가 육초 후, 완성 된 후에 만 나타납니다.

주석을 달고 PRINT사용 RAISERROR하면 SSMS의 메시지 탭에있는 메시지가 6 초 동안 기다리지 않고 루프가 진행되는 동안 나타납니다.

흥미롭게 RAISERROR도와를 모두 사용하면 PRINT두 메시지가 모두 표시됩니다. 먼저 first에서 메시지를 가져온 RAISERROR다음 2 초 동안 지연 한 다음 first PRINT및 second RAISERROR등으로 메시지를 보냅니다 .


다른 경우에는 별도의 전용 log테이블을 사용 하고 장기 실행 프로세스의 현재 상태 및 타임 스탬프를 설명하는 정보와 함께 행을 테이블에 삽입하기 만하면됩니다.

긴 과정이 주기적으로 I을 실행되는 동안 SELECT에서 log진행되는 상황을 확인하기 위해 테이블.

이것은 분명히 오버 헤드가 있지만 나중에 나 자신의 속도로 조사 할 수있는 로그 (또는 로그 기록)를 남깁니다.


답변

다음과 같은 다른 연결에서 모니터링 할 수 있습니다.

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

남은 금액을 볼 수 있습니다. 응용 프로그램이 SSMS 등에서 수동으로 프로세스를 실행하지 않고 프로세스를 호출하는 경우 진행 상황을 표시해야하는 경우에 유용 할 수 있습니다. “비동기 호출 (또는 스레드)이 완료 될 때까지 매번 확인하십시오.

격리 수준을 가능한 한 느슨하게 설정하면 잠금 문제로 인해 주 트랜잭션 뒤에 걸리지 않고 적절한 시간 내에 반환되어야합니다. 그것은 반환 값이 약간 부정확하다는 것을 의미 할 수 있지만 간단한 진행 측정기로 이것은 전혀 중요하지 않습니다.