SQL SERVER 2022-21-CREATE INDEX의 WAIT_AT_LOW_PRIORITY 옵션

소개

인덱스를 온라인 옵션으로 생성할 때 WAIT_AT_LOW_PRIORITY 추가 옵션이 SQL Server 2017부터 ALTER INDEX 문으로 지원되었습니다. SQL Server 2022 (또는 Azure SQL)부터는 CREATE INDEX 문에서도 지원합니다. 그에 따르는 추가 옵션 MAX_DURATION과 ABORT_AFTER_WAIT 옵션을 알아보겠습니다.

참고 자료

• CREATE INDEX 에서 WAIT_AT_LOW_PRIORITY 옵션

WAIT_AT_LOW_PRIORITY 옵션

온라인 인덱스와 관련된 다양한 옵션들과 기능들이 보다 세심하고 넓은 범위로 지원을 확대하는 것 같습니다, 그 만큼 글로벌하게 실무적인 필요성이 높다는 의미겠지요. 새로운 옵션은 또 다른 필요를 낳는 법이니…

WAIT_AT_LOW_PRIORITY 옵션은 이전의 ALTER INDEX 문으로 지원되던 것이니 해당 기능은 이미 알려져있습니다만 그래도 처음 접하시는 독자들을 위해 간단히 요약하겠습니다, 자세한 내용은 위 MS 링크 문서를 참조하세요.

온라인으로 인덱스를 생성할 때 다른 작업과 충돌(차단)이 발생하는 경우 우선 순위를 낮춰서 대기하도록 지정하는 옵션입니다. 이 때 최대 대기 시간과 대기 후 동작 방식을 지정하는 아래 하위 옵션들이 추가로 제공됩니다.

구문과 동작

• MAX_DURATION = time [MINUTES]
  차단(충돌) 발생 시 대기하는 최대 시간을 분 단위로 지정합니다.
• ABORT_AFTER_WAIT = [{NONE | SELF | BLOCKERS}]
  NONE은 기존과 동일한 동작입니다. MAX_DURATION이 지정(0이 아닌 값)된 경우 이후 동작을 선택합니다. SELF는 인덱스 작업을 취소하는 것이고, BLOCKER는 차단 원인이 되는 사용자 트랜잭션/세션을 취소시킵니다.

위 내용을 기준으로 하면 (MAX_DURATION = 0, ABORT_AFTER_WAIT = NONE)이 WAIT_AT_LOW_PRIORITY를 사용하지 않을 때의 기본 동작과 동일한 셈입니다.

그러면 간단한 예제 코드로 테스트 하겠습니다.

대용량 테이블이 필요한 관계로 개인적으로 가지고 있는 150만건의 Orders 테이블을 가지고 아래와 같이 재현해 보았습니다.

[세션-A에서 실행할 코드] UPDATE 후 2분동안 대기

BEGIN TRAN

UPDATE Orders
SET ORDERDATE += 365
WHERE ORDERDATE = '1993-05-30 00:00:00.000'

WAITFOR DELAY '00:02:00'

ROLLBACK
SELECT @@TRANCOUNT;

[세션-B에서 실행할 코드] 세션-A 실행과 동시에 CREATE INDEX 수행, 대기(차단) 시 1분간 기다린 후 SELF | BLOCKERS 취소 옵션을 각각 적용한 경우의 결과 확인합니다.

CREATE INDEX IX_Orders_OrderDate
ON dbo.Orders (OrderDate)
WITH (
ONLINE = ON (
WAIT_AT_LOW_PRIORITY (
MAX_DURATION = 1 MINUTES
, ABORT_AFTER_WAIT = SELF     -- SELF | BLOCKERS
)
)
)

[정리

[그림-1] ABORT_AFTER_WAIT = SELF 일 때

CREATE INDEX 를 수행한 세션에서 아래와 같이 1222번 오류가 발생했습니다.

[그림-2] ABORT_AFTER_WAIT = BLOCKERS일 때

UPDATE 트랜잭션을 수행한 세션에서 아래와 같이 1218번 오류가 발생하고 세션이 끊어졌습니다.

정리

온라인 인덱스 옵션은 아직까지 Enterprise Edition 전용이죠 해서 Standard Edition 등의 사용자들은 그림의 떡과 같습니다만 확장된 옵션들이 현업에서 유용하게 사용되길 기대해 봅니다.

 

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