SQL SERVER 2022-20-재시작 가능한 PK와 UQ 온라인 생성

소개

인덱스를 온라인 옵션으로 생성 시 필요에 따라 일시 정지해 두고 이후 다시 재시작하거나 중지할 수 있는 방법이 SQL Server 2019(또는 Azure SQL)부터 지원되었습니다. SQL Server 2022부터는 Primary Key와 Unique Key 제약 조건을 생할 때도 동일한 기능을 지원합니다, PK와 UQ도 인덱스로 생성되기 때문이죠.

참고 자료

• 다시 시작 가능한 테이블 제약조건 추가

재시작 가능한 PK/UQ 온라인 생성

SQL Server 2022 (또는 Azure SQL)에서 기존 테이블에 Primary Key 혹은 UNIQUE Key 생성 시 ONLINE 옵션을 사용하는 경우 생성 작업이 완료되기 전에 일시중지한 뒤 필요할 때 다시 재시작하거나 작업을 취소할 수 있는 RESUABLE 옵션을 추가로 지원합니다.

간단한 개념이니 아래 예제 코드로 충분히 설명이 될겁니다. 시간이 오래 걸리는 작업이 필요해서 개인적으로 만든 DB와 테이블을 사용했습니다, 실제로 돌려보고 싶으시다면 대용량 테이블 하나 만들어서 작업하시기 바랍니다. 간단히 주석도 달았으니 참고하세요. (전반적인 설명은 위 참고 자료의 MS 문서를 보시기 바랍니다)

USE SalesLab;
GO

-- 현재 PK, UQ 확인
EXEC sp_helpconstraint N'dbo.Orders'

/*
1. Resumable PK/UNIQUE 작업
*/
ALTER TABLE dbo.Orders
ADD CONSTRAINT UQ_Orders UNIQUE (OrderKey) -- 주의. CL 최대 크기는 900bytes
WITH (ONLINE = ON
, MAXDOP = 8
, RESUMABLE = ON
, MAX_DURATION = 60 MINUTES);
GO -- 50 ~ 70s

/*
2. 다른 세션에서, Pause/Resume/Abort 그리고 상태 확인
*/
--상태 확인
SELECT i.percent_complete, i.page_count, *
FROM sys.index_resumable_operations AS i;

-- Pause
ALTER INDEX UQ_Orders -- 또는 ALL
ON dbo.Orders
PAUSE;
GO

-- Resume
ALTER INDEX UQ_Orders -- 또는 ALL
ON dbo.Orders
RESUME
WITH
(
MAXDOP = 8 -- 이전 수와 동일해야 함
, MAX_DURATION = 60 MINUTES
, WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = SELF) -- NONE/SELF/BLOCKERS
) ;
GO

-- Abort
ALTER INDEX UQ_Orders
ON dbo.Orders
ABORT;
GO

[그림-1] 생성 작업 중에 sys.index_resumable_operations 를 수행한 결과

진행율(percent_complete), I/O량(page_count), 상태, 이름, 쿼리, 병렬처리 CPU수(last_max_dop_used) 등의 상태 정보를 실시간으로 확인할 수 있습니다.

정리

대용량 테이블과 인덱스를 운영하고 있고 인덱스 Rebuild 작업을 위해 서비스 중지시간이 충분하지 않은 경우나 혹은 ONLINE 작업에 방해 요소가 발생할 수 있다면 이러한 옵션을 활용할 수 있겠습니다. 적용하신다면 꼭 사전에 예행연습을 해 보시길 권합니다.

모두 건강하세요.
김정선 드림