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

  • 아티클

적용 대상: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

온라인 인덱스 만들기 및 다시 작성을 위한 다시 시작 가능한 작업은 SQL Server 2019, Azure SQL Database 및 Azure SQL Managed Instance에 대해 이미 지원됩니다. 다시 시작 가능 작업을 사용하면 테이블이 온라인(ONLINE=ON) 상태인 동안 인덱스 작업을 실행할 수 있으며 다음 작업도 수행할 수 있습니다.

  • 유지 관리 기간에 맞게 인덱스 만들기 또는 다시 빌드 작업을 여러 번 일시 중지 및 다시 시작

  • 인덱스 만들기 또는 다시 빌드 실패(예: 데이터베이스 장애 조치(failover) 또는 디스크 공간 부족)에서 복구.

  • 인덱스 만들기 또는 다시 빌드 작업 중 트랜잭션 로그 잘라내기 사용.

  • 인덱스 작업이 일시 중지된 경우 원래 인덱스와 새로 만든 인덱스 모두 저장할 디스크 공간이 필요하며 DML(데이터 조작 언어) 작업 중 업데이트해야 합니다.

SQL Server 2022, SQL Database 및 SQL Managed Instance에 대한 새 확장을 사용하면 DDL(데이터 정의 언어) 명령 ALTER TABLE ADD CONSTRAINT에 대한 다시 시작 가능한 작업을 허용하고 기본 또는 고유 키를 추가할 수 있습니다. 기본 또는 고유 키를 추가하는 방법에 대한 자세한 내용은 ALTER TABLE table_constraint를 참조하세요.

참고 항목

다시 시작 가능한 테이블 추가 제약 조건은 PRIMARY KEY 및 UNIQUE KEY 제약 조건에만 적용됩니다. FOREIGN KEY 제약 조건에는 다시 시작 가능한 테이블 추가 제약 조건이 지원되지 않습니다.

다시 시작 가능한 작업

이전 버전의 SQL Server에서는 ONLINE=ON 옵션을 사용하여 ALTER TABLE ADD CONSTRAINT 작업을 실행할 수 있습니다. 그러나 큰 테이블이 완료되려면 작업에 몇 시간이 걸릴 수 있으며 많은 수의 리소스가 사용될 수 있습니다. 이러한 실행 중에 실패 또는 중단이 발생할 수도 있습니다. 사용자가 유지 관리 기간 동안 작업을 일시 중지하거나 처음부터 작업을 다시 시작하지 않고 실행 실패 도중 중단된 위치에서 다시 시작할 수 있도록 다시 시작 가능한 ALTER TABLE ADD CONSTRAINT 기능을 도입했습니다.

지원되는 시나리오

새로운 ALTER TABLE ADD CONSTRAINT의 다시 시작 가능 기능은 다음과 같은 고객 시나리오를 지원합니다.

  • 유지 관리 기간 동안 일시 중지하고 유지 관리 기간이 완료되면 작업을 다시 시작하는 등 실행 중인 ALTER TABLE ADD CONSTRAINT 작업을 일시 중지하거나 다시 시작합니다.

  • 장애 조치(failover) 및 시스템 실패 후 ALTER TABLE ADD CONSTRAINT 작업을 다시 시작합니다.

  • 사용 가능한 로그 크기가 작더라도 큰 테이블에서 ALTER TABLE ADD CONSTRAINT 작업을 실행합니다.

참고 항목

ALTER TABLE ADD CONSTRAINT의 다시 시작 가능한 작업에서는 ALTER 명령을 온라인으로 실행해야 합니다(WITH ONLINE = ON).

이 기능은 큰 테이블에 특히 유용합니다.

ALTER TABLE의 T-SQL 구문

테이블 제약 조건에서 다시 시작 가능한 작업을 사용하도록 설정하는 데 사용되는 구문에 대한 자세한 내용은 ALTER TABLE(Transact-SQL)의 구문 및 옵션을 참조하세요.

ALTER TABLE에 대한 설명

  • ALTER TABLE(Transact-SQL)의 현재 T-SQL 구문에 새로운 절 WITH <resumable_options가 추가되었습니다.

  • RESUMABLE 옵션은 새 옵션이며, 기존 ALTER TABLE(Transact-SQL) 구문에 추가되었습니다.

  • MAX_DURATION = RESUMABLE = ON에 사용된 time [MINUTES](ONLINE = ON 필요). MAX_DURATION은 다시 시작 가능한 온라인 제약 조건 추가 작업이 일시 중지되기 전에 실행되는 시간을 나타냅니다(분 단위로 지정된 정수 값). 지정하지 않으면 작업이 완료될 때까지 계속됩니다.

ALTER INDEX의 T-SQL 구문

ALTER TABLE ADD CONSTRAINT의 다시 시작 가능한 테이블 제약 조건 작업을 일시 중지, 다시 시작 또는 중단하려면 T-SQL 구문 ALTER INDEX(Transact-SQL)를 사용합니다.

다시 시작 가능한 제약 조건의 경우 기존 ALTER INDEX ALL 명령이 사용됩니다.

ALTER INDEX ALL ON <table_name>  
      { RESUME [WITH (<resumable_index_options>,[...n])]
        | PAUSE
        | ABORT
      }
<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }
 <low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}

ALTER INDEX에 대한 설명

ALTER INDEX ALL ON <Table> PAUSE

  • 실행 중인 다시 시작 가능 및 온라인 테이블 제약 조건 추가 작업 일시 중지

ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]

  • 수동으로 일시 중지되거나 실패로 인해 발생한 테이블 제약 조건 추가 작업을 다시 시작합니다.

RESUMABLE=ON 에 사용되는 MAX_DURATION

  • 다시 시작된 후 다시 시작 가능한 테이블 제약 조건 추가 작업이 실행되는 시간(분 단위로 지정된 정수 값)입니다. 시간이 만료된 후 다시 시작할 수 있는 작업이 여전히 실행 중인 경우 일시 중지됩니다.

RESUMABLE=ON 및 ONLINE = ON에 사용되는 WAIT_AT_LOW_PRIORITY

  • 일시 중지 후 온라인 테이블 제약 조건 추가 작업을 다시 시작하려면 이 테이블에서 차단 작업을 기다려야 합니다. WAIT_AT_LOW_PRIORITY는 다시 시작 가능 작업이 대기하는 동안 다른 작업을 진행할 수 있도록 테이블 제약 조건 추가 작업이 낮은 우선 순위 잠금을 기다림을 나타냅니다. WAIT_AT_LOW_PRIORITY 옵션을 생략하면 WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE)와 동일합니다. 자세한 내용은 WAIT_AT_LOW_PRIORITY를 참조하세요.

ALTER INDEX ALL ON <Table> ABORT

  • 다시 시작 가능으로 선언된 실행 중이거나 일시 중지된 테이블 제약 조건 추가 작업을 중단합니다. 다시 시작 가능한 제약 조건 작업을 종료하려면 중단 작업을 ABORT 명령으로 명시적으로 실행해야 합니다. 다시 시작 가능한 테이블 제약 조건 작업 실패나 일시 중지는 실행을 종료하지 않습니다. 대신 작업은 무기한 일시 중지 상태가 됩니다.

다시 시작 가능한 작업에 사용할 수 있는 PAUSE, RESUME, ABORT 옵션에 대한 자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.

다시 시작 가능한 작업의 상태 보기

다시 시작 가능한 테이블 제약 조건 작업의 상태를 보려면 sys.index_resumable_operations 보기를 사용합니다.

사용 권한

ALTER 테이블에 대한 권한이 필요합니다.

다시 시작 가능한 ALTER TABLE ADD CONSTRAINT에 대한 새 권한은 필요하지 않습니다.

예제

다음은 다시 시작 가능한 테이블 제약 조건 추가 작업 사용에 대한 몇 가지 예제입니다.

예 1

MAX_DURATION이 240분인 열 (a)에 클러스터된 기본 키를 추가하기 위한 다시 시작할 수 있는 ALTER TABLE 작업입니다.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

예제 2

MAX_DURATION이 240분인 두 개의 열(a 및 b)에서 고유한 제약 조건을 추가하기 위한 다시 시작 가능 ALTER TABLE 작업.

ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

예 3

일시 중지되고 다시 시작되는 클러스터된 기본 키를 추가하기 위한 ALTER TABLE 작업.

아래 표는 다음 T-SQL 문을 사용하여 시간순으로 실행되는 두 세션(Session #1 및 Session #2)을 보여 줍니다. Session #1은 Col1 열에 기본 키를 만드는 다시 시작 가능한 ALTER TABLE ADD CONSTRAINT 작업을 실행합니다. Session #2는 실행 중인 제약 조건의 실행 상태를 확인합니다. 잠시 후 재사용 가능한 작업을 일시 중지합니다. Session #2는 일시 중지된 제약 조건의 상태를 확인합니다. 마지막으로 Session #1은 일시 중지된 제약 조건을 다시 시작하고 Session #2는 상태를 다시 확인합니다.

세션 #1 세션 #2
다시 시작 가능한 추가 제약 조건 실행

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
제약 조건 상태 확인

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
작업을 보여 주는 출력

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING43.552
다시 시작 가능한 제약 조건 일시 중지

ALTER INDEX ALL ON TestConstraint PAUSE;
오류

Msg 1219, Level 16, State 1, Line 6
Your session has been disconnected because of a high priority DDL operation.

Msg 1750, Level 16, State 1, Line 6
Could not create constraint or index. See previous errors.

Msg 0, Level 20, State 0, Line 5
A severe error occurred on the current command.
The results, if any, should be discarded.
제약 조건 상태 확인

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
작업을 보여 주는 출력

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)일시 중지됨65.339
ALTER INDEX ALL ON TestConstraint RESUME;
제약 조건 상태 확인

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
작업을 보여 주는 출력

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)RUNNING90.238

작업이 완료되면 다음 T-SQL 문을 실행하여 제약 조건을 확인합니다.

SELECT constraint_name, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO

결과 세트:

constraint_name table_name constraint_type
PK_Constraint TestConstraint PRIMARY KEY

참고 항목